topic Re: CASE statement in QlikView in QlikView

CASE statement in QlikView

Thu, 07 May 2015 12:25:47 GMT

I want to use this ABCD

CASE WHEN COUNTRY_CODE IN ('AU','CN','CS','SG','IN') THEN

(CASE WHEN SUM(ABCD_UNIT) > 500 THEN 'A'

WHEN SUM(ABCD_UNIT) > 250 THEN 'B'

WHEN SUM(ABCD_UNIT) > 50 THEN 'C' ELSE 'D' END)

ELSE

(CASE WHEN SUM(ABCD_UNIT) > 250 THEN 'A'

WHEN SUM(ABCD_UNIT) > 50 THEN 'B'

WHEN SUM(ABCD_UNIT) > 10 THEN 'C' ELSE 'D' END)

END AS 'ABCD

Thanks

sivaram

Re: CASE statement in QlikView

Peter_Cammaert — Thu, 07 May 2015 12:45:17 GMT

Where do you want to use this? Load script or Expression (UI)?

Re: CASE statement in QlikView

Thu, 07 May 2015 12:49:50 GMT

YES

Re: CASE statement in QlikView

Peter_Cammaert — Thu, 07 May 2015 13:09:45 GMT

YES what? Load script or Expression (UI)? Select one please...

Re: CASE statement in QlikView

Thu, 07 May 2015 13:22:17 GMT

Load Script dear

Re: CASE statement in QlikView

Peter_Cammaert — Thu, 07 May 2015 13:30:02 GMT

Use a LEFT JOIN and GROUP BY clause to aggregate your facts, translate them into codes (A, B, C and D) and attach them back to your facts.

Your example however doesn't contain enough information to write it out in QV Script.

Peter

Re: CASE statement in QlikView

nagaiank — Thu, 07 May 2015 13:44:34 GMT

If you have table named 'Data' with two fields COUNTRY_CODE, and ABCD_UNIT previously loaded, you may create a second table named 'ABCD_Data' with the fields COUNTRY_CODE and ABCD using the following script:

ABCD_Data:

LOAD COUNTRY_CODE

,If(Match(COUNTRY_CODE,'AU','CN','CS','SG','IN')>0

,If(X>500,'A',If(X>250,'B',If(X>50,'C','D')))

,If(X>250,'A',If(X>50, 'B',If(X>10,'C','D')))

) as ABCD;

LOAD COUNTRY_CODE, Sum(ABCD_UNIT) as X Resident Data;

Re: CASE statement in QlikView

Fri, 08 May 2015 10:37:47 GMT

SQL SELECT COUNTRY_CODE,SHIP_YEAR,TM1_VOL_FLAG,TRANSPORT_MODE,

CASE WHEN COUNTRY_CODE IN ('AU','CN','CS','SG','IN') THEN

(CASE WHEN SUM(CHARGEABLE_WEIGHT) > 500 THEN 'A'

WHEN SUM(CHARGEABLE_WEIGHT) > 250 THEN 'B'

WHEN SUM(CHARGEABLE_WEIGHT) > 50 THEN 'C' ELSE 'D' END)

ELSE

(CASE WHEN SUM(CHARGEABLE_WEIGHT) > 250 THEN 'A'

WHEN SUM(CHARGEABLE_WEIGHT) > 50 THEN 'B'

WHEN SUM(CHARGEABLE_WEIGHT) > 10 THEN 'C' ELSE 'D' END)

END AS 'ABCD' ,SUM(CHARGEABLE_WEIGHT)as Total

FROM "Siv_DailyUpdate_Test".dbo.TEST GROUP BY COUNTRY_CODE,SHIP_YEAR,TM1_VOL_FLAG,TRANSPORT_MODE;

This is working fine i can create ABCD

SAME code is used using QVD

LOAD COUNTRY_CODE,SHIP_YEAR,TM1_VOL_FLAG,TRANSPORT_MODE,

CASE WHEN COUNTRY_CODE IN ('AU','CN','CS','SG','IN') THEN

(CASE WHEN SUM(CHARGEABLE_WEIGHT) > 500 THEN 'A'

WHEN SUM(CHARGEABLE_WEIGHT) > 250 THEN 'B'

WHEN SUM(CHARGEABLE_WEIGHT) > 50 THEN 'C' ELSE 'D' END)

ELSE

(CASE WHEN SUM(CHARGEABLE_WEIGHT) > 250 THEN 'A'

WHEN SUM(CHARGEABLE_WEIGHT) > 50 THEN 'B'

WHEN SUM(CHARGEABLE_WEIGHT) > 10 THEN 'C' ELSE 'D' END)

END AS 'ABCD' ,SUM(CHARGEABLE_WEIGHT)as Total

From $(vQVD_Path)SHIP_ALL_INDIA_NEW.qvd(qvd) GROUP BY COUNTRY_CODE,SHIP_YEAR,TM1_VOL_FLAG,TRANSPORT_MODE;

IT IS SHOWING IN RED AT CASE SIR

Thanks

sivaram

Re: CASE statement in QlikView

Peter_Cammaert — Fri, 08 May 2015 11:15:13 GMT

Of course, because SWITCH CASE is a QlikView Control statement that cannot be used inside a LOAD statement. You have to translate all conditional evaluations into IF() functions, like

LOAD COUNTRY_CODE,SHIP_YEAR,TM1_VOL_FLAG,TRANSPORT_MODE,

IF (Match(COUNTRY_CODE, 'AU','CN','CS','SG','IN'),

IF (Sum(CHARGEABLE_WEIGHT) > 500, 'A',

IF (Sum(CHARGEABLE_WEIGHT) > 250, 'B',

IF (Sum(CHARGEABLE_WEIGHT) > 50, 'C', 'D'))),

IF (Sum(CHARGEABLE_WEIGHT) > 250, 'A',

IF (Sum(CHARGEABLE_WEIGHT) > 50, 'B',

IF (Sum(CHARGEABLE_WEIGHT) > 10, 'C', 'D')))) AS ABCD,

Sum(CHARGEABLE_WEIGHT) AS Total

FROM $(vQVD_Path)SHIP_ALL_INDIA_NEW.qvd(qvd)

GROUP BY COUNTRY_CODE,SHIP_YEAR,TM1_VOL_FLAG,TRANSPORT_MODE;

Use the syntax checker to check if all parentheses match.

Best,

Peter