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;