How to use the WHILE Loop in a Turbo Integrator Process

While is a Turbo Integrator (TI) function that is widely used to “loop” through data repeatedly, without wrapping multiple processes together to accomplish the same goal.

Below is the syntax:

Define loop iteration element

WHILE ( logical expression );

Statements;

loop iteration;

END;

Let’s illustrate it by using a simple WHILE loop example:

We want to find and insert leaf (or 0 or N) level elements into a subset, whose ancestor is “Net Operating Expense”.  In this example, our subset name is “All Operating Expenses” in the Account dimension.

#1         DimName = ‘Account’;

#2         Ancestor = ‘Net Operating Expense’;

#3         SubsetName = ‘All Operating Expenses’;

#4         index = 1;

#5         WHILE ( index <= DIMSIZ ( DimName ) );

#6                       elmName = DIMNM ( DimName, index );

#7                       IF ( ELLEV ( DimName, elmName ) = 0 & ELISANC ( DimName, Ancestor, elmName ) = 1 );

#8                                   SUBSETELEMENTINSERT ( DimName, SubsetName, elmeName, 1 );

#9                        ENDIF;

#10                     Index = index + 1;

#11       END;

Now let’s decipher the above WHILE loop

Line #4 is the defined loop iteration element

Line #5 is the logical expression, index <= DIMSIZ (DimName), which defines when the loop should stop.  In this example, the loop processes all elements within Account dimension

line #6 through #9 define the steps to take if the element meets the criteria . In this example, the loop checks whether the element is 0 Level and its ancestor is “Net Operating Expense”, if the condition is met, the element will be inserted into the subset, “All Operating Expenses”

Line #10 is the loop iteration, which moves the element processing forward. This is a very important step to avoid implementing an infinite loop.

Is it possible to stop the loop? The answer is yes. We can use the BREAK function to break out of a loop at any point. Let’s use it in the above example. Instead of inserting all 0 level elements whose ancestor is “Net Operating Expense”, we want to only insert the first ten 0 level elements that meet the condition. Here is what the syntax should be look like

#1         DimName = ‘Account’;

#2         Ancestor = ‘Net Operating Expense’;

#3         SubsetName = ‘All Operating Expenses’;

#4         index = 1;

#5         icounter = 1;

#6         WHILE ( index <= DIMSIZ ( DimName ) );

#7                       elmName = DIMNM ( DimName, index );

#8                       IF ( ELLEV ( DimName, elmName ) = 0 & ELISANC ( DimName, Ancestor, elmName ) = 1);

#9                                   SUBSETELEMENTINSERT ( DimName, SubsetName, elmeName, 1 );

#10                                    icounter = incounter + 1;

#11                                    IF ( icounter > 10 );

#12                                                  BREAK;

#13                                    ENDIF;

#14                      ENDIF;

#15                     Index = index + 1;

#16       END;