TM1 Scan Function

Post Reply
macklovesraine11
Posts: 11
Joined: Wed Sep 28, 2016 1:05 am
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

TM1 Scan Function

Post by macklovesraine11 » Tue Jun 13, 2017 3:40 am

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
Community Contributor
Posts: 275
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: Cognos TM 1
Version: 10.2.2
Excel Version: 2013
Location: Sydney, Australia

Re: TM1 Scan Function

Post by EvgenyT » Tue Jun 13, 2017 3:48 am

macklovesraine11 wrote: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
Posts: 11
Joined: Wed Sep 28, 2016 1:05 am
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Scan Function

Post by macklovesraine11 » Tue Jun 13, 2017 4:05 am

Sorry I haven't tried out yet.

User avatar
Alan Kirk
Site Admin
Posts: 5996
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: TM1 Scan Function

Post by Alan Kirk » Tue Jun 13, 2017 4:34 am

macklovesraine11 wrote:Sorry I haven't tried out yet.
Have you at least read the documentation on the Scan and SubSt functions yet?
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Mark RMBC
Regular Participant
Posts: 200
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 Scan Function

Post by Mark RMBC » Tue Jun 13, 2017 1:06 pm

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
Posts: 11
Joined: Wed Sep 28, 2016 1:05 am
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Scan Function

Post by macklovesraine11 » Tue Jun 13, 2017 11:16 pm

Mark RMBC wrote: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
MVP
Posts: 2259
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TM1 Scan Function

Post by Wim Gielis » Wed Jun 14, 2017 12:01 am

Hi Michael,

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

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

AmbPin
Regular Participant
Posts: 173
Joined: Sat Mar 20, 2010 3:03 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007-10

Re: TM1 Scan Function

Post by AmbPin » Wed Jun 14, 2017 7:41 am

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
Regular Participant
Posts: 200
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 Scan Function

Post by Mark RMBC » Wed Jun 14, 2017 11:12 am

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
Posts: 11
Joined: Wed Sep 28, 2016 1:05 am
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Scan Function

Post by macklovesraine11 » Wed Jun 14, 2017 11:52 pm

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
MVP
Posts: 2259
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TM1 Scan Function

Post by Wim Gielis » Thu Jun 15, 2017 11:35 am

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

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Post Reply