Hi gurus,
Is there a way to do wildcard search in a TI process?
If (element like Rad*)
Any help as always is appreciated
Wildcard text searches in a TI process
-
- Posts: 24
- Joined: Mon Jul 19, 2021 2:15 pm
- OLAP Product: TM1
- Version: 2.0.0
- Excel Version: 365
-
- MVP
- Posts: 3103
- 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: Wildcard text searches in a TI process
No, Like does not exist.
You would use Subst. Whether it’s easy or straightforward to have the same logic is a different matter.
You could also think about adding elements to a dimension, which is not used in a cube. Then use MDX Tm1FilterbyPattern for wildcards ? and *
I have seen someone support some types of regular expressions in TM1, but admittedly it’s not a walk in the park.
You could also call scripts like Python or VbScript or PowerShell or similar, with more advanced logic. For example, to do regular expressions.
You would use Subst. Whether it’s easy or straightforward to have the same logic is a different matter.
You could also think about adding elements to a dimension, which is not used in a cube. Then use MDX Tm1FilterbyPattern for wildcards ? and *
I have seen someone support some types of regular expressions in TM1, but admittedly it’s not a walk in the park.
You could also call scripts like Python or VbScript or PowerShell or similar, with more advanced logic. For example, to do regular expressions.
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
-
- Posts: 24
- Joined: Mon Jul 19, 2021 2:15 pm
- OLAP Product: TM1
- Version: 2.0.0
- Excel Version: 365
- Michel Zijlema
- Site Admin
- Posts: 712
- Joined: Wed May 14, 2008 5:22 am
- OLAP Product: TM1, PALO
- Version: both 2.5 and higher
- Excel Version: 2003-2007-2010
- Location: Netherlands
- Contact:
Re: Wildcard text searches in a TI process
Not really a full wildcard search functionality, but for your example you could look at the SCAN function:
F.i. SCAN('Rad', 'Radius') returns 1, meaning that the string 'Radius' starts with the substring 'Rad'.
If the function returns 0 the substring is not found in the string. If the return value is > 1 than the string contains the substring, but not at the starting position of the string.
Note that the SCAN function is case sensitive.
F.i. SCAN('Rad', 'Radius') returns 1, meaning that the string 'Radius' starts with the substring 'Rad'.
If the function returns 0 the substring is not found in the string. If the return value is > 1 than the string contains the substring, but not at the starting position of the string.
Note that the SCAN function is case sensitive.
-
- Posts: 24
- Joined: Mon Jul 19, 2021 2:15 pm
- OLAP Product: TM1
- Version: 2.0.0
- Excel Version: 365
Re: Wildcard text searches in a TI process
Some more details as I have gotten farther.
In the Data Source Query of my TI process I have the following section that returns properly in the Preview
CASE WHEN upper(A.TOWN) like '%HALIFAX%NS%' or upper(A.TOWN) like '%BEDFORD%NS%' or upper(A.TOWN) like '%DARTMOUTH%NS%' then 'Y' ELSE 'N'END AS NS_METRO_FLAG
But when I try to add the same section to the vSQL on the Advanced - Prolog tab I get an error saying I need a semi colon.
'A.CREDIT_TIME_CODE AS NET_DAYS, ' |
'A.CREDIT_LINE, ' |
'A.NUM_BUDGET_PAYMENTS, ' |
'CASE WHEN upper(A.TOWN) like '*HALIFAX*NS*' % upper(A.TOWN) like '*BEDFORD*NS*' % upper(A.TOWN) like '*DARTMOUTH*NS*' then 'Y' ELSE 'N' END AS NS_METRO_FLAG ' |
I also tried it exactly the way it is on the Data Source but I figured I had to change it because % means or in a TI process
In the Data Source Query of my TI process I have the following section that returns properly in the Preview
CASE WHEN upper(A.TOWN) like '%HALIFAX%NS%' or upper(A.TOWN) like '%BEDFORD%NS%' or upper(A.TOWN) like '%DARTMOUTH%NS%' then 'Y' ELSE 'N'END AS NS_METRO_FLAG
But when I try to add the same section to the vSQL on the Advanced - Prolog tab I get an error saying I need a semi colon.
'A.CREDIT_TIME_CODE AS NET_DAYS, ' |
'A.CREDIT_LINE, ' |
'A.NUM_BUDGET_PAYMENTS, ' |
'CASE WHEN upper(A.TOWN) like '*HALIFAX*NS*' % upper(A.TOWN) like '*BEDFORD*NS*' % upper(A.TOWN) like '*DARTMOUTH*NS*' then 'Y' ELSE 'N' END AS NS_METRO_FLAG ' |
I also tried it exactly the way it is on the Data Source but I figured I had to change it because % means or in a TI process
-
- Regular Participant
- Posts: 346
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: Wildcard text searches in a TI process
Hi,
% does not mean or when it is between single quotes. So I don't believe that is your problem.
Given you have single quotes in your case statement, you will need to escape them, best to use char(39) instead of a quote I think.
And of course, all statements need to end with a semi colon, so I would expect vSQL to end with a semi colon.
Maren
% does not mean or when it is between single quotes. So I don't believe that is your problem.
Given you have single quotes in your case statement, you will need to escape them, best to use char(39) instead of a quote I think.
And of course, all statements need to end with a semi colon, so I would expect vSQL to end with a semi colon.
Maren