TM1 Forum

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

If (element like Rad*)

Any help as always is appreciated

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.

thank you

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.

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

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