Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Case statement Replacement

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


1 Solution

Accepted Solutions
anbu1984
Master III
Master III

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 ...

View solution in original post

13 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

Hi Raj,

Please close this thread by making appropriate answer if you are satisfied...

Regards,

@Sub2u

Not applicable
Author

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.

Not applicable
Author

Sure Sub2u, I will do it.

Not applicable
Author

Hi Gysbert,
Sorry, I got it. Let me execute.

Thank you.

Not applicable
Author

Hi,

I am not able to get it. I am getting some error, I will post script part tomorrow.

Thank you,

Not applicable
Author

Hi,

Here is my script part. Please check attachment and please suggest me for replacement of Case statements.

Thank you,

flipside
Partner - Specialist II
Partner - Specialist II

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