CASE statement in QlikView

[#1]

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

[Peter_Cammaert]

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

[#3]

YES

[Peter_Cammaert]

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

[#5]

Load Script dear

[Peter_Cammaert]

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

[nagaiank]

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;

[#8]

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

[Peter_Cammaert · Accepted solution]

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