How to use Decode in qliksense ?

[rupalimane1390]

Hello,

I am trying to convert below decode from SQL query in qliksense, I tried to use pick & match but it failed as it skips values other than the conditional values of transaction type passed to decode.

decode(sign(it.x_value) || tt.description,
'0Switch', 'Switch In', '1Switch', 'Switch In', '-1Switch', 'Switch Out',
'0Transfer', 'Transfer In', '1Transfer', 'Transfer In', '-1Transfer', 'Transfer Out',
nvl( tt.description, 'Other' ) ) as transaction_type,

Note: where description is transaction_type as Switch,Transfer etc.

[rubenmarin]

Hi, maybe there is a better way but this could be something like:

If(Match(Sign(it.x_value) & tt.description,'0Switch', 'Switch In', '1Switch', 'Switch In', '-1Switch', 'Switch Out',
'0Transfer', 'Transfer In', '1Transfer', 'Transfer In', '-1Transfer', 'Transfer Out'), tt.description, 'Other') as transaction_type,

[rupalimane1390]

Hi @rubenmarin ,

Thank you for your reply. This is not working as we need to flag 'other' only when transaction description is blank.

I found a solution  as below 🙂

f(isnull(DESCRIPTION),'other',        // Flagging as 'other' when DESCRIPTIONis null
pick(match(sign(X_VALUE)&''&DESCRIPTION,                      // Pick '0switch' then match as 'Switch In' and so on
'0Switch','1Switch','-1Switch','0Transfer','1Transfer','-1Transfer')+1,DESCRIPTION,      
'Switch In','Switch In','Switch Out','Transfer In''Transfer In','Transfer Out')) as TRANSACTION_TYPE                    //Display DESCRIPTION as it is when values are different than values passed in Pick and Match.