TM1 Scan Function
-
- 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
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!
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!
-
- Community Contributor
- Posts: 324
- Joined: Mon Jul 02, 2012 9:39 pm
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: 2016
- Location: Sydney, Australia
Re: TM1 Scan Function
Are you able to provide an example you attempting to use SCAN function?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!
-
- 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
Sorry I haven't tried out yet.
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: TM1 Scan Function
Have you at least read the documentation on the Scan and SubSt functions yet?macklovesraine11 wrote:Sorry I haven't tried out 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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: TM1 Scan Function
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:
may suffice.
But more information would mean less assumptions on my part!
cheers, mark
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);
But more information would mean less assumptions on my part!
cheers, mark
-
- 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
Hi Mark,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:
may suffice.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);
But more information would mean less assumptions on my part!
cheers, 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
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Scan Function
Hi Michael,
Consider the functions Scan, Subst, Long.
That should do it.
Consider the functions Scan, Subst, Long.
That should do it.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- 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
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.
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.
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: TM1 Scan Function
Well then you could add into the code above:
and change the following:
Assuming all the assumptions are valid!
Code: Select all
vScanA = Scan('|', vScan);
Code: Select all
vScanNSW = subst(vScan,1,vScanA-1);
vScanSS = Subst(vScan,vScanA+1,long(vScan));
-
- 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
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
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
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Scan Function
Hi, thank you for the feedback, glad it's solved !
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly