TM1’s REPLACE function for rules and TI


Admittedly, the title of this article is a kind of a teaser, since euhm, TM1 has NO built-in REPLACE function for rules and TI. For the illustration, let’s say that you have a subject line for a customized email message:

vOriginal = 'Please fill in the data for [FCST_PERIOD]. Thank you.';

Here, you have [FCST_PERIOD] as a placeholder, to be substituted for a parameter value in a TI process:

vReplaceWhat = '[FCST_PERIOD]';
pPeriod = 'April 2012';
(a user fills in the parameter value for pPeriod at run-time)


You have this option which is often used. Concatenate 3 parts - first part of the original text, parameter value, last part of the original text:

vReplaced = Subst(vOriginal,1,Scan(vReplaceWhat,vOriginal)-1) | pPeriod | Subst(vOriginal,Scan(vReplaceWhat,vOriginal)+Long(vReplaceWhat),Long(vOriginal));
vReplaced = Subst(vOriginal,1,Scan(vReplaceWhat,vOriginal)-1);
vReplaced = vReplaced | pPeriod | Subst(vOriginal,Scan(vReplaceWhat,vOriginal)+Long(vReplaceWhat),Long(vOriginal));

My second suggestion is quite interesting, in that it uses functions that I normally do not use in TM1: INSRT and DELET.

vReplaced = Insrt(pPeriod,Delet(vOriginal,Scan(vReplaceWhat,vOriginal),Long(vReplaceWhat)),Scan(vReplaceWhat,vOriginal));

I like this approach. From inside to outside, we first cut out the parameter name in the original text (DELET). Then, we insert the new parameter value chosen by the user. We do this at the correct character position.


Oftentimes, in TM1 models we need to go back and forth to work out element names based on other names. Or calculate changes from one period to the other. For example, we start with 'P04 YTD' to get 'P05 YTD' (year-to-date consolidations in a period dimension). Here, do not use the replace operations mentioned above, to replace 4 by 5. Rather, use an attribute on the respective dimension. Similarly, adding an alias to a dimension is a very good alternative to string operations of the kind I explained.


Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links