The if function returns a value depending on whether the condition provided with the function evaluates as True or False.
if(condition , then [, else])
The if function has three parameters, condition, then and else, which are all expressions. The two other ones, then and else, can be of any type.
|condition||Expression that is interpreted logically.|
|then||Expression that can be of any type. If the condition is True, then the if function returns the value of the then expression.|
Expression that can be of any type. If the condition is False, then the if function returns the value of the else expression.
This parameter is optional. If the condition is False, NULL is returned if you have not specified else.
if( Amount>= 0, 'OK', 'Alarm' )
|This expression will test if the amount is a positive number (0 or larger) and return 'OK' if it is. If the amount is less than 0, 'Alarm' is returned.|
|Date||Location||Incidents||if(Incidents>=10, 'Critical', 'Ok' )||if(Incidents>=10, 'Critical', If( Incidents>=1 and Incidents<10, 'Warning', 'Ok'))|
If can be used in load script with other methods and objects, including variables. For example, if you set a variable threshold and want to include a field in the data model based on that threshold, you can do the following:
Transactions: Load * Inline [ transaction_id, transaction_date, transaction_amount, transaction_quantity, customer_id, size, color_code 3750, 20180830, 23.56, 2, 2038593, L, Red 3751, 20180907, 556.31, 6, 203521, m, orange 3752, 20180916, 5.75, 1, 5646471, S, blue 3753, 20180922, 125.00, 7, 3036491, l, Black 3754, 20180922, 484.21, 13, 049681, xs, Red 3756, 20180922, 59.18, 2, 2038593, M, Blue 3757, 20180923, 177.42, 21, 203521, XL, Black ]; set threshold = 100; /* Create new table called Transaction_Buckets Compare transaction_amount field from Transaction table to threshold of 100. Output results into a new field called Compared to Threshold */ Transaction_Buckets: Load transaction_id, If(transaction_amount > $(threshold),'Greater than $(threshold)','Less than $(threshold)') as [Compared to Threshold] Resident Transactions;
|transaction_id||Compared to Threshold|
|3750||Less than 100|
|3751||Greater than 100|
|3752||Less than 100|
|3753||Greater than 100|
|3754||Greater than 100|
|3756||Less than 100|
|3757||Greater than 100|