HI,
Working on a query that if one field is null then it uses another field and if that field isnull it uses another. Will case work like that in a linear operation left-to-right or is there a better option?
eval main=case(isnull(test1),test2,test1,isnull(test2),test3,test2,isnull(test3),test4,test3,1=1,"All Test Are Null)
Try this,
| eval main = case(isnotnull(test1), test1, isnotnull(test2), test2, isnotnull(test3), test3, isnotnull(test4), test4, 1=1 , "All Test Are Null")
Main's value should be test1 / test2 / test3 / test4 in-case test1 is empty option goes to test2, if test2 is empty then option goes to test 3 and test4 like wise.
If suppose test1, test2, test3, test4 contains value then test1 would be assigned to main. if not "All Test are Null" will be assigned to main.
Hope this will help you.
Take a look at the eval coalesce function - as the documentation states
This function takes an arbitrary number of arguments and returns the first value that is not null.
Try this,
| eval main = case(isnotnull(test1), test1, isnotnull(test2), test2, isnotnull(test3), test3, isnotnull(test4), test4, 1=1 , "All Test Are Null")
Main's value should be test1 / test2 / test3 / test4 in-case test1 is empty option goes to test2, if test2 is empty then option goes to test 3 and test4 like wise.
If suppose test1, test2, test3, test4 contains value then test1 would be assigned to main. if not "All Test are Null" will be assigned to main.
Hope this will help you.
Thanks - worked like a dream
Cheers !!!