Regular expressions in TI

Ideas and tips for enhancing your TM1 application
Post Reply
pandinus
Posts: 78
Joined: Tue Mar 18, 2014 8:02 am
OLAP Product: TM1, Cognos Express
Version: 10.2.2
Excel Version: 2013

Regular expressions in TI

Post by pandinus »

Based on my reply in this thread here is the same post again, ready to be moved to the appropriate forum.


For some more elaborate and dynamic find/replace functions in TI, I was forced to do some cool stuff a while ago: regular expressions.
While not being exactly regular expressions as per definition, they offer a lot of flexibility and have become my golden hammer in many situations.

It works like this:
  • Define your original string
  • Define your filter expression
  • Define any replacement rules.

Example 1:
Here we do a simple replace. Replace any space characters with underscores.

Code: Select all

vString = 'TM1 is great!';
vExpression = ' ';
vReplacement = '_';
Becomes

Code: Select all

TM1_is_great!

Example 2:
Here we do not only want to replace, we want to add to a matched string. For this we use the $& to refer to the matched string.

Code: Select all

vString = 'CellPutN(vValue, vCubename, cm_dim1, cm_dim2, cm_dim3)';
vExpression = 'CellPut?(*,*,';
vReplacement = '$& cm_extra, ';
Becomes

Code: Select all

CellPutN(vValue, vCubename, cm_extra, cm_dim1, cm_dim2, cm_dim3)

Example 3:
When not replacing stuff, sometimes we just want just look for stuff in strings. This will give you the FIRST occurrence of the match based on the expression in the string, and the length of the matched string.

Code: Select all

vString = 'TM1 is relatively ok!';
vExpression = 'el';
Becomes

Code: Select all

"TM1 is relatively ok!","el","9","2"
When using wildcards:

Code: Select all

vString = 'TM1 is relatively ok!';
vExpression = '1*!';
Becomes

Code: Select all

"TM1 is relatively ok!","1*!","3","19"

Known limitations/features:
  • The parser is case-insensitive
  • The parser runs maximum of 256 runs over the same string. This is deemed sufficient. Increase this value at your own risk
  • When adding to the matched string, you can enter text in front of the $& or behind it. Or both.
  • While not necessary, you should feed logic instructions to the parser. Feeding c*?s works as does c*s to find Cognos
  • There is no way to escape characters used for the parser. This means that you are not able to find strings matching asterisk or question mark signs.
  • No support for 'true' regular expressions such as ^[a-zA-Z0-9_.+-]
I have attached the required .pro files to this post for your entertainment.
You run the Regex_test process for testing.
The logic is in the Sys_regex_replace and Sys_regex_find processes.
Attachments
Sys_Regex_find.pro
(5.97 KiB) Downloaded 614 times
Regex_test.pro
(3.07 KiB) Downloaded 611 times
Sys_Regex_replace.pro
(8.32 KiB) Downloaded 584 times
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Regular expressions in TI

Post by jim wood »

Moved to the correct forum. Thanks for moving it to it's own post to make the movement easier,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
hju
Posts: 7
Joined: Wed Sep 09, 2015 8:01 pm
OLAP Product: TM1 / PA
Version: 11 (PA 2.0)
Excel Version: 2010

Re: Regular expressions in TI

Post by hju »

thx pandinus for this very useful code.

I'm sorry but I think I found a small glitch in the Sys_Regex_find.pro code.

If the start of an expression is found at the end of the string but string ends before the expression is clompletely parsed there is a "false positive" result.
Please have a look at the following example:
String = "73257"
Expression = "73207"
results in Regex_find_position=1

The first round works as expexted. A match is found until position 4 of the string.
But with expression moving throug the string, the first "7" of expression matches the 7 at position 5 of string again.
Unfortunately the process then ignores the rest of expression which could not be tested against string.

I implemented the following "patch":
I inserted a test if there is some expression left if string parsed ended before to clear the result vMatchString

Code: Select all

  IF(vStrPos > vStrLen);
    vMatchString = '';
  ENDIF;
in the outer loop

Code: Select all

vExprPos = 1;
WHILE(vExprPos <= vExprLen & vUsedOptions <= vMaxOptions);

  ###############
  # Loop over string characters
  ###############
  vFound = 0;
  vStrPos = 1 + vStrPosMatch + vStrPosEntry;
>  IF(vStrPos > vStrLen);
>    vMatchString = '';
>  ENDIF;
  WHILE(vStrPos <= vStrLen);
I'm not sure if the patch works 100%. I tested it with some of my cases and it seems to work as expected... but there may be side effects.
Post Reply