How to Pad a Variable Length Element in TM1

Often we have a situation were we want to create a dimension with the contents of a code from a source system.  Sometimes, however the IDs in the source system are of a variable length and we want to present them uniformly.  To overcome this, we need to pad the code at the left with a constant character to make them all the same length. the method below will pad out the left side of the ID with zeroes in this instance (probably what you want to use if the original IDs are numeric).

Method

Firstly, convert the number to a string using NumberToString() function in TI. This will convert 10 to string “10”.

Second, use the LEN() function to get the length of the string. This will return you length of 2 for string”10″.

Finally, use the IF condition to append zeros before the string per the following code:

IF (LEN(vString) = 1);
vString = "0000" | vString;
ELSEIF (LEN(vString) = 2);
vString = "000" | vString;
ELSEIF (LEN(vString) = 3);
vString = "000" | vString;
ELSEIF (LEN(vString) = 4);
vString = "00" | vString;
ELSEIF (LEN(vString) = 5);
vString = "0" | vString;
ENDIF;

Usage

Typically we would use this when we are updating a dimension.  Often it would be combined with a DimensionElementInsert to insert the element into the dimension and DimensionSortOrder to then get the elements in the dimension into the correct order (obviously after the padding has been done!).

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.
This entry was posted in Elements, TI Processes. Bookmark the permalink.