topic Re: How to use Decode in qliksense ? in App Development

How to use Decode in qliksense ?

rupalimane1390 — Thu, 17 Jun 2021 12:34:53 GMT

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.

Re: How to use Decode in qliksense ?

rubenmarin — Sat, 19 Jun 2021 07:54:19 GMT

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,

Re: How to use Decode in qliksense ?

rupalimane1390 — Mon, 21 Jun 2021 11:16:32 GMT

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.