Excerpt 

In your recipe steps, you can apply conditional logic to determine if transformational changes should occur. 
You can build logical tests into your transformations in multiple levels:
 Single and multicase transformations: Use casebased transformations to test ifthen or case logic against your dataset and to apply the specified results.
 Conditional functions: IF and CASE functions can be applied to any transformation that accepts functional expressions.
 Logical operators: You can use AND or OR logic to build your conditional expressions.
Through the Transform Builder, you can build conditional tests using if/then/else or case logic to manipulate on the data.
 In the Search panel in the Transformer page, enter
case
.  You can choose one of three different logical transformations:
 Ifthenelse: Specify any logical test that evaluates to
true
or false
and specify values if true
(then) or if false
(else).  Singlecolumn case: Test for explicit values in a column and, if true, write specific values to the new column.
 Custom conditions: Specify any number of case statements, which can have completely independent expressions:
 Case 1 is tested, and a value is written if
true
.  If Case 1 is false, then Case 2 is tested. If
true
, a different value can be written.  Supports an arbitrary number of independent conditional cases.
 Specify the other parameters, including the name of the new column.
After the transformation is added to the recipe, actions can then be taken based on the values in this new column.
You can also apply conditional logical as part of your function definitions for other transformations.
For example, the following replaces values in the same column with IN
if they are greater than 0.5 or OUT
otherwise:
D trans 

Type  step 

p01Name  Columns 

p01Value  testCol 

p02Name  Formula 

p02Value  IF($col >= 0.5, 'IN','OUT') 

SearchTerm  Edit column with formula 


In the above, the token $col
is a reference back to the value defined for the column (testCol
in this case). However, you can replace it with a reference to any column in the dataset.
You can use the IF function in any transformation that accepts functional inputs.
You can chain together IF functions in the following manner:
D trans 

Type  step 

p01Name  Columns 

p01Value  testCol 

p02Name  Formula 

p02Value  IF($col >= 0.5, 'IN',(IF($col >= 0.35, 'MAYBE IN','OUT'))) 

SearchTerm  Edit column with formula 


However, these can become problematic to debug. Instead, you can use the CASE function to assist in building more complex logical trees. The following is more legible and easier to manage:
D trans 

Type  step 

p01Name  Columns 

p01Value  testCol 

p02Name  Formula 

p02Value  CASE([ $col >= 0.75, 'IN', $col >= 0.35, 'MAYBE IN', 'OUT']) 

SearchTerm  Edit column with formula 


If test  Test  Output if true 

If:  $col >= 0.75  IN 
If above is false :  $col >= 0.35
 MAYBE IN 
If above is false :  default  OUT 
Logical operators can be applied to your function expressions to expand the range of your logical tests.
In the above example, suppose you have a second column called, Paid
, which contains Boolean values. You could expand the previous statement to include a test to see if Paid=true
:
D trans 

Type  step 

p01Name  Columns 

p01Value  testCol 

p02Name  Formula 

p02Value  CASE([ ($col >= 0.75 && Paid == true), 'IN', ($col >= 0.35 && Paid == true), 'MAYBE IN', 'OUT']) 

SearchTerm  Edit column with formula 


The above performs a logical AND operation on the two expressions in each tested case. The logical operator is &&
.
You can also reference explicit functions to perform logical tests. The above might be replaced with the following:
D trans 

Type  step 

p01Name  Columns 

p01Value  testCol 

p02Name  Formula 

p02Value  CASE([ AND($col >= 0.75, Paid == true), 'IN', AND($col >= 0.35, Paid == true), 'MAYBE IN', 'OUT']) 

SearchTerm  Edit column with formula 


Logic  Logical Operator  Logical Function 

Logical AND  (exp1 && exp2)  AND(exp1,exp2) 
Logical OR  (exp1  exp2)  OR(exp1,exp2) 
Logical NOT  !(exp1 == exp2)  NOT(exp1,exp2) 
Depending on the structure of your transformation and your preferences, either form may be used.
D s also 

inCQL  true 

label  (label = "logic") 

