TM1’s REPLACE function for rules and TI
- Dec. 27, 2022
Introduction
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)
Options
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 ));or:
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.
An interesting suggestion is the EXPAND function. It is by far the easiest solution:
vOriginal = 'Please fill in the data for [%pPeriod%]. Thank you.'; vReplaced = Expand( vOriginal );
That's all there is to it ! just use the % characters surrounding a variable to have it updated at run-time as TM1 knows the value of pPeriod.
Attributes
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.
