A powerful Data transformation language similar to Pascal is included in all our ETL and Business automation tools.

It can be used with Calculation Transformation FunctionPackage Script Action or inside any text object

##### 1. Simple if then else

Here is an example of how the if statement works:

var
number : Integer;
text   : String;
begin
number := Sqr(17);          // Calculate the square of 17
if number > 400
then text := '17 squared > 400'    // Action when if condition is true
else text := '17 squared <= 400';  // Action when if condition is false
result:=text;
end;

text is set to : '17 squared <= 400'

There are a number of things to note about the if statement. First that it spans a few lines - remember that statements can span lines - this is why it insists on a terminating ;
Second, that the then statement does not have a terminating; -this is because it is part of the if statement, which is finished at the end of the else clause.

Third, we have set the value of a text string when If the condition is successful - the Then clause - and when unsuccessful - the Else clause. We could have just done a then assignment:

if number > 400
then text := '17 squared > 400';

Note that here, the then condition is not executed (because 17 squared is not > 400), but there is no else clause. This means that the if statement simply finishes without doing anything.

Note also that the then clause now has a terminating; to signify the end of the if statement.

Compound if conditions, and multiple statements
We can have multiple conditions for the if condition. And we can have more than one statement for the then and else clauses. Here are some examples:

If (condition1) And (condition2)   // Both conditions must be satisfied
then
begin
statement1;
statement2;
...
end              // Notice no terminating ';' - still part of 'if'
else
begin
statement3;
statement4;
...
end;

We used And to join the if conditions together - both must be satisfied for the then clause to execute. Otherwise, the else clause will execute. We could have used a number of different logical primitives, of which And is one, covered under logical primitives below.

Nested if statements:

There is nothing to stop you from using if statements as the statement of an if statement. Nesting can be useful, and is often used like this:

if condition1
then statement1
else if condition2
then statement2
else statement3;

However, too many nested if statements can make the code confusing. The Case statement, discussed below, can be used to overcome a lot of these problems.

##### 2 Logical primitives

Before we introduce these, it is appropriate to introduce the Boolean data type. It is an enumerated type that can have one of only two values: True or False. We will use it in place of a condition in the if clauses below to clarify how they work:

begin
if false And false
then Result:='false and false = true';

if true And false
then Result:= 'true  and false = true';

if false And true
then Result:= 'false and true  = true';

if true And true
then Result:= 'true  and true  = true';

if false Or false
then Result:= 'false or  false = true';

if true Or false
then Result:= 'true  or  false = true';

if false Or true
then Result:= 'false or  true  = true';

if true Or true
then Result:= 'true  or  true  = true';

if false Xor false
then Result:= 'false xor false = true';

if true Xor false
then Result:= 'true  xor false = true';

if false Xor true
then Result:= 'false xor true  = true';

if true Xor true
then Result:= 'true  xor true  = true';

if Not false
then Result:= 'not false  = true';

if Not true
then Result:= 'not true  = true';
end;

• true  and true  = true
• false or  true      = true
• true  or  false     = true
• true  or  true      = true
• false xor true  = true
• true  xor false = true
• not false = true

Note that the Xor primitive returns true when one, but not both of the conditions are true.

##### 3 Case statements

The "If" statement is useful when you have a simple two-way decision. Ether you go one way or another way. Case statements are used when you have a set of 3 or more alternatives.

A simple numerical case statement:

var
i : Integer;
begin
i := StrToInt([F1]);
Case i of
15 : Resut := ('Number was fifteen');
16 : Resut := ('Number was sixteen');
17 : Resut := ('Number was seventeen');
18 : Resut := ('Number was eighteen');
19 : Resut := ('Number was nineteen');
20 : Resut := ('Number was twenty');
end;
end;

Number was fifteen

The case statement above routes the processing to just one of the statements. OK, the code is a bit silly, but it is used to illustrate the point.

##### Using the otherwise clause

Supposing we were not entirely sure what value our case statement was processing? Or we wanted to cover a known set of values in one fell swoop? The Else clause allows us to do that:

var
i : Integer;
begin
i := [F1];
Case i of
15 : Resut := ‘Number was fifteen';
16 : Resut := 'Number was sixteen';
17 : Resut := 'Number was seventeen';
18 : Resut := 'Number was eighteen';
19 : Resut := 'Number was nineteen';
20 : Resut := 'Nuumber was twenty';
else
Resut := 'Unexpected number‘;
end;
end;

Unexpected number: 10

Next: Repeating sets of commands