IF Construct

The IF instruction allows performing different calculations according to a logical condition. In this case, the syntax to use is

… := if then else end;

For example, we can write:

[LO].[X00000] := if [C00000] == 0 then [D00000] else [E00000] end;

Generally, the result of the IF instruction is:

  • when the is true;
  • when the is false.

Technically, in order to evaluate the IF expression, the system runs the following operation

* + *

that, in some cases, may give wrong results. For further details, please see Advanced usage of the BOUND function.

The syntax * is an alternative way to get all combinations between and the values for which the is true.

The IF instruction can be nested and the condition ELSE can be omitted.

Example 1

[LO].@CONDITION := [C00000] == 10;

[LO].[X00000] := @CONDITION * [D00000](Dest1.[D16]);

Additionally, let us assume the following data are on the database.

ACCOUNT C00000

Entity Account Scenario Category CustDim1 CustDim2 Period Amount
A00 C00000 2013BDG $AMOUNT D11 D21 01 10
A00 C00000 2013BDG $AMOUNT D12 D21 01 10
A00 C00000 2013BDG $AMOUNT D13 D21 01 30
A00 C00000 2013BDG $AMOUNT D14 D21 01 50
A00 C00000 2013BDG $AMOUNT D15 D21 01 10

ACCOUNT D00000

Entity Account Scenario Category CustDim1 CustDim2 Period Amount
A00 D00000 2013BDG $AMOUNT D16 D21 01 20

When evaluating @CONDITION, the system generates as many records as existing records for the account C00000 by assigning the value 1 where the condition is verified, otherwise 0. We will get the following values:

Entity CustDim1 CustDim2 Amount
A00 D11 D21 1
A00 D12 D21 1
A00 D13 D21 0
A00 D14 D21 0
A00 D15 D21 1

The result of our script will be:

Entity Account Scenario Category CustDim1 CustDim2 Period Amount
A00 X00000 2013BDG $AMOUNT D11 D21 01 20*1
A00 X00000 2013BDG $AMOUNT D12 D21 01 20 *1
A00 X00000 2013BDG $AMOUNT D15 D21 01 20*1

Example 2

[LO].@CONDITION := Dest1 <> “xxx”;

[LO].[X00000] := @CONDITION * [D00000](Dest1.[GEN]);

This is another way to use the IF condition in order to deploy an account on a given dimension (in this case, the custom dimension 1). the system evaluates the @CONDITION by creating a list of records for each element of the custom dimension 1 with code different from xxx. If the record with code xxx doesn't exist, it means that we shall open as may records with amount 1 as elements of the custom dimension 1.