Wildcard text searches in a TI process

Post Reply
M1ndbender
Posts: 24
Joined: Mon Jul 19, 2021 2:15 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: 365

Wildcard text searches in a TI process

Post by M1ndbender »

Hi gurus,
Is there a way to do wildcard search in a TI process?

If (element like Rad*)

Any help as always is appreciated
Wim Gielis
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

Post by Wim Gielis »

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.
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
M1ndbender
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

Post by M1ndbender »

thank you
User avatar
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

Post by Michel Zijlema »

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.
M1ndbender
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

Post by M1ndbender »

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
MarenC
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

Post by MarenC »

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
Post Reply