Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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


Tags (1)
1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: Case statement Replacement

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

13 Replies

Re: Case statement Replacement

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

Re: Case statement Replacement

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

Re: Case statement Replacement

Hi Raj,

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

Regards,

@Sub2u

Not applicable

Re: Case statement Replacement

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

Re: Case statement Replacement

Sure Sub2u, I will do it.

Not applicable

Re: Case statement Replacement

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

Thank you.

Not applicable

Re: Case statement Replacement

Hi,

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

Thank you,

Not applicable

Re: Case statement Replacement

Hi,

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

Thank you,

flipside
Valued Contributor II

Re: Case statement Replacement

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


Community Browser