TM1 TI Scan Function

Post Reply
AlphaWay
Posts: 4
Joined: Wed May 20, 2020 6:23 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: Version 2003

TM1 TI Scan Function

Post by AlphaWay » Wed May 20, 2020 8:08 am

Hi All,

Good day.

Could you ppl help me to separate country name from a string(column). Say i have 1000 rows in country column , each row has its code and country name with "-" separated. Some has single "-" & some has "-"*"-" like eg:1
Please see the following example:

Eg:1
001-1ab-ab15 - Singapore
or
Eg:2
123 - India

I would want to extract the 'Singapore' and 'India' from that string into variables to load in cube. How can I possibly do this in TM1?

Thanks in advance!

declanr
MVP
Posts: 1659
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: TM1 TI Scan Function

Post by declanr » Wed May 20, 2020 8:18 am

Code: Select all

sFind = '- ';
nLong = Long ( vsString );
iPos = nLong - 1;
nFound = 0;
While ( iPos > 0 & nFound = 0 );
	sSub = SubString ( vsString, iPos, 2 );
	If ( sSub @= sFind );
		nFound = 1;
	Else;
		iPos = iPos - 1;
	EndIf;
End;

If ( nFound = 0 );
	LogOutput ( 'INFO', 'Could not find country in string: ' | vsString );
Else;
	sCountry = SubString ( vsString, iPos + 2, nLong - iPos - 1 );
EndIf;
Declan Rodger

AlphaWay
Posts: 4
Joined: Wed May 20, 2020 6:23 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: Version 2003

Re: TM1 TI Scan Function

Post by AlphaWay » Wed May 20, 2020 10:05 am

Thank you so much Declan for inputs! Based on your code, i got the exact result and output looks good.

Post Reply