topic Re: Case statement Replacement in QlikView

Case statement Replacement

Thu, 25 Dec 2014 13:11:04 GMT

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

Re: Case statement Replacement

Gysbert_Wassenaar — Thu, 25 Dec 2014 13:22:35 GMT

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.

Re: Case statement Replacement

Thu, 25 Dec 2014 13:29:04 GMT

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

Re: Case statement Replacement

Thu, 25 Dec 2014 13:49:27 GMT

Hi Raj,

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

Regards,

@Sub2u

Re: Case statement Replacement

Thu, 25 Dec 2014 13:50:51 GMT

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.

Re: Case statement Replacement

Thu, 25 Dec 2014 13:53:31 GMT

Sure Sub2u, I will do it.

Re: Case statement Replacement

Thu, 25 Dec 2014 14:39:08 GMT

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

Thank you.

Re: Case statement Replacement

Sun, 28 Dec 2014 07:02:29 GMT

Hi,

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

Thank you,

Re: Case statement Replacement

Mon, 29 Dec 2014 07:52:28 GMT

Hi,

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

Thank you,

Re: Case statement Replacement

flipside — Mon, 29 Dec 2014 09:22:26 GMT

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

Re: Case statement Replacement

Mon, 29 Dec 2014 11:58:45 GMT

Hi,

My data is from SYBASE- Database. So is IF statement will support at script

Thank you.

Re: Case statement Replacement

anbu1984 — Mon, 29 Dec 2014 12:12:21 GMT

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

Re: Case statement Replacement

flipside — Mon, 29 Dec 2014 13:58:11 GMT

The IF statement being suggested here is being executed by the Qlikview engine because it is written in the 'preceding load' part of the script. It doesn't matter from which database the data comes.

When writing code that comes after the SQL SELECT part, this is executed by the database engine so needs to comply with the database query language AND also be supported by the driver being used to connect to the database.

Re: Case statement Replacement

Mon, 29 Dec 2014 14:45:44 GMT

Thanks Anbu,

I could understand clearly and I made it at my work.

Thank you.