TM1 Forum

Posted: **Wed Aug 17, 2016 5:59 pm**

I have a cube that has 3 dimension: EmpName, DeptNum, SupName. The values in EmpName are in the format Name - Employee Number (so as an example it might look like this Jones, Tom - 1234 (same with SupName). 2 records might look like this:

"Jones, Tom - 1234","ABC123","Barker, Bob - 9876"
"Placeholder1","ABC123","Rabbit, Roger - 4561"

I would like to extract the data into a CSV file (I'm using TEXTOUTPUT), but I want to truncate the Employee Number for real employees (1st record), but there isn't trucation needed for the Placeholder employees (as they don't have an employee number. I know that I'm always truncating the last 7 characters. In excel I do something like =LEFT(cell,len(cell)-7).

So it should look like this
"Jones, Tom","ABC123","Barker, Bob"
"Placeholder1","ABC123","Rabbit, Roger"

Not sure what coding I need to do in my TI to accomplish this. I got the extract part (TEXTOUTPUT) in the MetaData tab, just not sure about the text manipulation part.

Any help is appreciated

Thanks

Posted: **Wed Aug 17, 2016 6:33 pm**

Got the TextOutput part, just haven't figured out the "Placeholder" part

TextOutput('D:/filename.csv',SUBST(EmpName,1,long(EmpName)-7),RC,SUBST(SupName,1,long(SupName)-7));

Posted: **Wed Aug 17, 2016 6:33 pm**

You could use, for example, if the presence of - alone is enough to distinguish real names from placeholder names:

Code: Select all

If( Scan( '-', vLongName ) > 0 );
vShortName = Subst( vLongName, 1, Long( vLongName ) - 7);
Else;
vShortName = vLongName;
EndIf;

If needed, you can add additional logic to see if the last character is a number:

Code: Select all

If( Scan( Subst( vLongName, Long( vLongName ), 1 ), '0123456789' ) > 0);
...
EndIf;

TM1 Forum

Simple TI code

Simple TI code

Re: Simple TI code

Re: Simple TI code