Bear with the newbie again
My end user has some complexity in the sense that I need to look at a column I am pulling in and then have some logic that says something like... look for the first space... then, go back and capture from that point back... that is more flexible than saying start with the first character and go three spaces... sometimes, the first three characters will be like 2.8 or 1.8 and that is what I want.. but some others will be like MSATA, etc. (non numbers and loger than 3 characters). I got this to work just perfectly in Excel using this formula:
=IF(ISERROR(VALUE(LEFT(A2,FIND(" ",A2)-1))),A2,VALUE(LEFT(A2,FIND(" ",A2)-1)))
I then thought, okay... I'll replace the cell (A2) with my dimension information: ('SSD MPN', !SSD MPN,'Module Form Factor')
To come up with this:
IF(ISERROR(VALUE(LEFT(('SSD MPN', !SSD MPN,'Module Form Factor'),FIND(" ",('SSD MPN', !SSD MPN,'Module Form Factor'))-1))),('SSD MPN', !SSD MPN,'Module Form Factor'),VALUE(LEFT(('SSD MPN', !SSD MPN,'Module Form Factor'),FIND(" ",('SSD MPN', !SSD MPN,'Module Form Factor'))-1)))
I then added: ['Jim'] = S: to the front of the above...
But, apparantly, I am not able to use Excel functions in TM1? The "Developers Guide" makes it seem as if I can... but the following is not working:
['Jim'] = S: IF(ISERROR(VALUE(LEFT(('SSD MPN', !SSD MPN,'Module Form Factor'),FIND(" ",('SSD MPN', !SSD MPN,'Module Form Factor'))-1))),('SSD MPN', !SSD MPN,'Module Form Factor'),VALUE(LEFT(('SSD MPN', !SSD MPN,'Module Form Factor'),FIND(" ",('SSD MPN', !SSD MPN,'Module Form Factor'))-1)))
I've tried adding DB infront of each ('SSD MPN', !SSD MPN......) but that doesn't work... I've tried all sorts of things..
But, I am seeing that if I do it manually, I don't even have the options of using "ISERROR," "VALUE," "LEFT," or "FIND" in the functions drop down... so am at a bit of a loss
Any suggestions? I've spent hours on this already... sigh... so thought I would finally reach out to the experts
Thanks a lot!