TM1 Forum

Hi all,

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!

Look for the IBM Cognos TM1 Reference Guide in the Help and check out the section Rules Functions. These are the only functions you will be allowed to use in a cube. There is no search and replace function but check out the following string functions INSRT, SCAN, SUBST. You can do what you are asking with a combination of those three.