Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
In my application so many CASE statements have been used to extract the data from database at script level. But today I got requirement that need to replace CASE statements with qlikview functions. But I am not able to find that. Could you please some one help me?
EX:
CASE WHEN A.N_ACRONYM = B.N_ACRONYM THEN 0 ELSE A.EAD END as EAD_WA
CASE WHEN CLOSEOUT_AGREEMENT_NUMBER = '0' THEN A.EAD ELSE A.NET_EAD END AS EAD,
A.EAD_M_UNCAPPED ,
CASE WHEN CLOSEOUT_AGREEMENT_NUMBER = '0' THEN A.EAD_UK ELSE A.NET_EAD_UK END AS
CASE WHEN A.N_ACRONYM = B.N_ACRONYM THEN 0 ELSE A.PD_FLOOR END as PD_FLOOR_WA,
CASE WHEN A.N_ACRONYM = B.N_ACRONYM THEN 0 ELSE A.EAD END as EAD_WA,
CASE WHEN A.N_ACRONYM = B.N_ACRONYM THEN 0 ELSE A.LGD END as LGD_WA
CASE WHEN CLOSEOUT_AGREEMENT_NUMBER = '0' THEN A.EAD ELSE A.NET_EAD END AS EAD,
A.EAD_M_UNCAPPED ,
CASE WHEN CLOSEOUT_AGREEMENT_NUMBER = '0' THEN A.EAD_UK ELSE A.NET_EAD_UK END AS EAD_UK ,
Thank you
You can use If in the script as below
Load
If(N_ACRONYM_A = N_ACRONYM_B, 0, EAD ) As EAD_WA,
If(CLOSEOUT_AGREEMENT_NUMBER = '0',EAD,NET_EAD) As EAD;
Sql Select A.N_ACRONYM As N_ACRONYM_A,
B.N_ACRONYM As N_ACRONYM_B,
...
From ...
In the examples you posted you can use an if statement:
IF(A.N_ACRONYM = B.N_ACRONYM, 0, A.EAD END) as EAD_WA
Note, that all the fields used in the statement must exist in the same table. You will have to join tables first if this is not already the case.
Hi Raj,
In place of Case stateement use IF Condition.
Ex :- CASE WHEN A.N_ACRONYM = B.N_ACRONYM THEN 0 ELSE A.PD_FLOOR END as PD_FLOOR_WA
IF (A.N_ACRONYM = B.N_ACRONYM THEN 0 ELSE A.PD_FLOOR) AS PD_FLOOR_WA
IF(CLOSEOUT_AGREEMENT_NUMBER = '0' THEN A.EAD ELSE A.NET_EAD) AS EAD
Please check this....
I hope its help ful for you.....
Thank you..
Sub2u
Hi Raj,
Please close this thread by making appropriate answer if you are satisfied...
Regards,
@Sub2u
Hi Gysbert,
Thanks for the reply. How to right for the below one.
CASE WHEN CLOSEOUT_AGREEMENT_NUMBER = '0' THEN A.EAD ELSE A.NET_EAD END AS EAD,
Thank you.
Sure Sub2u, I will do it.
Hi Gysbert,
Sorry, I got it. Let me execute.
Thank you.
Hi,
I am not able to get it. I am getting some error, I will post script part tomorrow.
Thank you,
Hi,
Here is my script part. Please check attachment and please suggest me for replacement of Case statements.
Thank you,
I'm assuming you know that the CASE statements in the SQL SELECT section need moving (and changing to IFs) to the LOAD section (known as the preceding load). As per Gysbert's advice, you will need to bring back all fields required in the comparison, so both A.N_ACRONYM and B.N_ACRONYM will be needed so you can compare them in the preceding load script. This means you will need to give one (or both if you prefer) an alias in the SQL script to distinguish them and use the alias in the LOAD script.
flipside