If structures in TM1

  • Sep. 13, 2012

This article shows the syntax notations for If-Then-Else structures in TM1.

Turbo Integrator processes

In order to keep the discussion clean and simple, let’s just fill a variable in a couple of ways: 

vAccount='620000';

If(Scan(Subst(vAccount,1,1),'67')>0);
  vAccountType = 'IS';
Else;
  vAccountType = 'BS';
EndIf;

Our general ledger system exports accounts and their monthly balances. In an Accounts dimension, we have a hierarchical structure representing the ledger. A Text attribute distinguishes between Income Statement (IS) and Balance Sheet (BS) accounts. In the code block above, you will see the notation of our If-Then-Else structure. (In TM1 we do not use Then)

You do not necessarily need to use an Else branch in the If. Here is an example in which we will not process accounts that are unknown to TM1: 

vAccount='620000';

If(Dimix('Accounts',vAccount)=0);
  ItemSkip;
EndIf;

Alternatively, TI allows you to write in a more condensed way, as: 

If(Dimix('Accounts',vAccount)=0, ItemSkip, 0);

Back to the first example. Due to e.g. bad formatting of input files, leading zeroes could screw up the account 'numbers'. Here we deal with that possibility in an ElseIf case: a further If in the false part of the first If. This way, we can also exclude accounts starting with '9' for example. 

vAccount='620000';

If(Scan(Subst(vAccount,1,1),'67')>0);
  vAccountType = 'IS';
ElseIf(Scan(Subst(vAccount,1,1),'12345')>0);
  vAccountType = 'BS';
Else;
  ItemSkip;
EndIf;

Different syntax possibilities for If

Concentrating on filling a variable, we have several options: 

vAccount='620000';

# 1. multiple lines
If(Scan(Subst(vAccount,1,1),'67')>0);
  vAccountType = 'IS';
Else;
  vAccountType = 'BS';
EndIf;

# 2. 1 line with variable on the left
vAccountType = If(Scan(Subst(vAccount,1,1),'67')>0,'IS','BS');

Or yet another example. Each TM1 developer will use the style of coding of his/her liking. 

If(Country @= '?');
  vCountry = 'Unknown country';
ElseIf(Country @= '');
  vCountry = Attrs('Customers',vCustomer,'Customer_Country');
Else;
  vCountry = Country;
EndIf;

# or:
vCountry = If(Country @= '?', 'Unknown country', If(Country @= '',Attrs('Customers',vCustomer,'Customer_Country'), Country));

Or from the same token: 

vDistrictManager = IF(DTYPE('MER_District Manager',vDistrictManager) @<> 'N','No District Manager',vDistrictManager);

TM1 rules and the If function

The syntax notation for TM1 rules goes like this: 

['# Articles']=N:IF(['Qty Sold']>0,1,0);

You can nest If clauses: 

['# Articles']=N:IF(['Qty Sold']>0,IF(['Revenue']>0,1,0),0);

# or:
['# Articles']=N:IF(['Qty Sold'] & ['Revenue']>0,1,0);

That’s it

We have discussed the If structures in TM1 and its possible notations syntax-wise. Unlike other programming languages, a Case structure does not exist. You will need to nest several possibilities in an ElseIf structure. Admittedly, these examples are very simple. But that was my goal. Feel free to elaborate and whip up some pretty advanced If structures in your next TI process or cube rule.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links