IsUnd function in TM1
- Oct. 22, 2016
Introduction
When you frequently use tools like Excel or TM1, you know that you will use the most common functions in a relatively short period of time. But you also realize that it will take you quite a while before you will have used the other say 5% of functions. Today is such a case, where I used the function IsUnd for the very first time in almost 10 years of TM1.
The case is as follows. A typical TI process copies data from cube A to cube B. For instance, B is a reporting cube on top of A. One measure is part of the data source in cube A, and using a loop over the measures in the last dimension of cube B, we do our usual CellGetN/S and CellPutN/S stuff.
But some measures in the source cube where rules-calculated and ended up to be an error value in the cube. The customer asked me to skip these "bad" cells and would correct the calculation later on. Hence, I needed a way to skip these cells, errors were only found in numeric cells. If you do CellGetN towards a cell containing an error, this generates an error in the message log. We want a way to skip these messages and continue with the next measure/cell.
Sample code
# Loop over measures
m = 1;
While( m <= Dimsiz( 'Sales_Rpt_Measure' ));
vMsr = Dimnm( 'Sales_Rpt_Measure', m );
If( Dtype( 'Sales_Rpt_Measure', vMsr ) @= 'C' );
Itemskip;
ElseIf( Dtype( 'Sales_Rpt_Measure', vMsr ) @= 'N' );
If( IsUnd( CellGetN( 'A', vProduct, vYear, vMsr )) = 0 );
CellPutN( CellGetN( 'A', vProduct, vYear, vMsr ), 'B', vProduct, vYear, vMsr );
EndIf;
ElseIf( Dtype( 'Sales_Rpt_Measure', vMsr ) @= 'S' );
CellPutS( CellGetS( 'A', vProduct, vYear, vMsr ), 'B', vProduct, vYear, vMsr );
EndIf;
m = m + 1;
End;
As you can see near the middle of the code, I loop over the measures dimension in cube 'B' (which contains elements that are also found in cube 'A'). IsUnd gives us 0 or 1. When it is not 1 I skip that measure.
