4.49 if - Conditional Statements

Format: if( $bool,num_1,num_2$ ), if( $bool,str_1,str_2$ ), if( $bool,date_1,date_2$ ),if( $bool,time_1,time_2$ ),if( $bool,bool_1,bool_2$ )

If the first parameter is true, return the second parameter, otherwise, return the third parameter. If the first parameter includes NULL value, return NULL value. Note that second and third argument should use the same parameter type.

Examples

Example 1: Basic Example

If the value in time column is less than 120000, return "AM", otherwise, return "PM".

$ more dat1.csv
id,time
1,101215
2,210110
3,
4,120001
$ mcal c='if(${time}<=120000,"AM","PM")' a=ampm i=dat1.csv o=rsl1.csv
#END# kgcal a=ampm c=if(${time}<=120000,"AM","PM") i=dat1.csv o=rsl1.csv
$ more rsl1.csv
id,time,ampm
1,101215,AM
2,210110,PM
3,,
4,120001,PM

Example 2: Different parameter types

The function returns an error if the character format is different in the second and third parameter.

$ mcal c='if(${time}<=120000,"am",1)' a=ampm i=dat1.csv o=rsl2.csv
#ERROR# unknown function or operator: if_BSN (kgcal)
$ more rsl2.csv

Example 3: Return boolean value on conditional statements

Read the value in the column using $b{fieldname}, if the value is “1” return true, if it is “0” return false, other values will be treated as NULL.

$ more dat2.csv
id,val
1,1
2,0
3,
4,-2
$ mcal c='if($b{val},"ture","false")' a=bool i=dat2.csv o=rsl3.csv
#END# kgcal a=bool c=if($b{val},"ture","false") i=dat2.csv o=rsl3.csv
$ more rsl3.csv
id,val,bool
1,1,ture
2,0,false
3,,
4,-2,

Example 4: Time format comparison

$ mcal c='if($t{time}<=0t120000,"am","pm")' a=ampm i=dat1.csv o=rsl4.csv
#END# kgcal a=ampm c=if($t{time}<=0t120000,"am","pm") i=dat1.csv o=rsl4.csv
$ more rsl4.csv
id,time,ampm
1,101215,am
2,210110,pm
3,,
4,120001,pm

Example 5: Nested if function

$ more dat3.csv
id,val
1,10
2,0
3,-5
4,0
$ mcal c='if(${val}>0,"plus",if(${val}<0,"minus","zero"))' a=sign i=dat3.csv o=rsl5.csv
#END# kgcal a=sign c=if(${val}>0,"plus",if(${val}<0,"minus","zero")) i=dat3.csv o=rsl5.csv
$ more rsl5.csv
id,val,sign
1,10,plus
2,0,zero
3,-5,minus
4,0,zero