TM1 Scan Function

[macklovesraine11]

Hello Guys,

Good day.

I'd like to know how can I extract sub-strings from a string. Please see the following example:

PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS

I would want to extract the 'NSW' and 'SS' from that string and store them in separate variables. How can I possibly do this in TM1? My senior said that I do a scan of the pipe.

Thanks in advance!

[EvgenyT]

Hello Guys,

Good day.

I'd like to know how can I extract sub-strings from a string. Please see the following example:

PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS

I would want to extract the 'NSW' and 'SS' from that string and store them in separate variables. How can I possibly do this in TM1? My senior said that I do a scan of the pipe.

Thanks in advance!

Are you able to provide an example you attempting to use SCAN function?

[macklovesraine11]

Sorry I haven't tried out yet.

[Alan Kirk]

Sorry I haven't tried out yet.

Have you at least read the documentation on the Scan and SubSt functions yet?

[Mark RMBC]

Do you want to extract NSW and SS from the string or do you want to dynamically extract the string after the first pipe and the string after the second pipe?

if you want to store NSW and SS in a variable then just say v1 = 'NSW' and v2 = 'SS'!

Of course I know this isn't what you want to do but it could be read in that way!

Assuming you want to find the string after the pipes and assuming the full string always includes 2 pipes and the string you want to extract always comes after the pipes and the string you want to extract after the first pipe is always 3 characters long and 2 characters long after the second pipe then something like:

Code: Select all

vScan = subst('PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS', scan('|', 'PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS') + 1,long('PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS'));

vScanNSW = subst(vScan,1,3);
vScanSS = Subst(vScan,5,2);

may suffice.

But more information would mean less assumptions on my part!

cheers, mark

[macklovesraine11]

Do you want to extract NSW and SS from the string or do you want to dynamically extract the string after the first pipe and the string after the second pipe?

if you want to store NSW and SS in a variable then just say v1 = 'NSW' and v2 = 'SS'!

Of course I know this isn't what you want to do but it could be read in that way!

Assuming you want to find the string after the pipes and assuming the full string always includes 2 pipes and the string you want to extract always comes after the pipes and the string you want to extract after the first pipe is always 3 characters long and 2 characters long after the second pipe then something like:

Code: Select all

vScan = subst('PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS', scan('|', 'PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS') + 1,long('PEPSI-MS-0870-3/PO:4500752654.30|NSW|SS'));

vScanNSW = subst(vScan,1,3);
vScanSS = Subst(vScan,5,2);

may suffice.

But more information would mean less assumptions on my part!

cheers, mark

Hi Mark,

Thanks for the input. I would want to dynamically extract sting after the first pipe and the string after the second pipe. Number of characters is not always 3 and 2, sometimes it can be 2 and 4, 3 and 3, 2 and 2. Something like that.

Thanks, hope this info helps
Michael

[Wim Gielis]

Hi Michael,

Consider the functions Scan, Subst, Long.
That should do it.

[AmbPin]

You haven't said where this string data comes from.
If it were to come as a text data source you might be able to set the delimiter to the pipe char which would split it automatically for you.

[Mark RMBC]

Well then you could add into the code above:

Code: Select all

vScanA = Scan('|', vScan);

and change the following:

Code: Select all

vScanNSW = subst(vScan,1,vScanA-1);
vScanSS = Subst(vScan,vScanA+1,long(vScan));

Assuming all the assumptions are valid!

[macklovesraine11]

Hi Guys,

Good day.

Thanks for all your inputs! I gained the idea based on your suggestions, it now gives the desired result and output looks good.

Thanks everyone!
Michael

[Wim Gielis]

Hi, thank you for the feedback, glad it's solved !