How to convert case when statement in qliksense

[rupaliqlik]

Hi team,

I want to convert below case when statement in qliksense script.

1.

(CASE WHEN A.WORK_PRT_GB = '01' THEN (CASE WHEN ISNULL(C.CONV_QTY, 0) = 0 THEN 0 ELSE ISNULL(A.TIME_07, 0) / ISNULL(C.CONV_QTY, 0) END) ELSE ISNULL(A.TIME_07, 0) * ISNULL(C.CONV_QTY, 0) END) AS Conv_Qty

Qliksense Script:-   If(WORK_PRT_GB = '01',If(IsNull(CONV_QTY) = -1,TIME_07/CONV_QTY, TIME_07*CONV_QTY))

It gives me wrong result.

2.

(CASE WHEN C.PROD_TYPE IN ('F', 'FF&P') THEN (CASE WHEN A.WORK_PRT_GB = '01' THEN (CASE WHEN ISNULL(C.CONV_QTY, 0) = 0 THEN 0 ELSE ISNULL(A.TIME_07, 0) / ISNULL(C.CONV_QTY, 0) END) ELSE ISNULL(A.TIME_07, 0) * ISNULL(C.CONV_QTY, 0) END) ELSE 0 END ) AS Formulation

3.
(CASE WHEN C.PROD_TYPE IN ( 'FF&P') THEN (CASE WHEN A.WORK_PRT_GB = '01' THEN (CASE WHEN ISNULL(C.CONV_QTY, 0) = 0 THEN 0 ELSE ISNULL(A.TIME_07, 0) / ISNULL(C.CONV_QTY, 0) END) ELSE ISNULL(A.TIME_07, 0) * ISNULL(C.CONV_QTY, 0) END) * 2
WHEN C.PROD_TYPE IN ('F', 'F&P') THEN (CASE WHEN A.WORK_PRT_GB = '01' THEN (CASE WHEN ISNULL(C.CONV_QTY, 0) = 0 THEN 0 ELSE ISNULL(A.TIME_07, 0) / ISNULL(C.CONV_QTY, 0) END) ELSE ISNULL(A.TIME_07, 0) * ISNULL(C.CONV_QTY, 0) END) ELSE 0 END ) AS Formulation_T

Please help me to convert these queries into qliksense backend script with the help of if else condition.

Best Regards,

Rupali Ethape

@sunny_talwar @jagan  

[abhijitnalekar]

Hi @rupaliqlik ,

Please check the below expression.

if(A.WORK_PRT_GB = '01',
if(Coalesce(C.CONV_QTY,0)=0,0, Coalesce(A.TIME_0,0)/Coalesce(C.CONV_QTY,0)
),
Coalesce(A.TIME_07, 0)*Coalesce(C.CONV_QTY, 0)
)

[rupaliqlik]

@abhijitnalekar 

Thanks,

But I want to convert this in qliksense script.

Coalesce function will not work here.

[abhijitnalekar]

Coalesce function will work

[rupaliqlik]

@abhijitnalekar 

I need your help to build below 2 logic. Please help me.

(CASE WHEN C.PROD_TYPE IN ('F&P', 'FF&P') THEN (CASE WHEN A.WORK_PRT_GB = '01' THEN (CASE WHEN ISNULL(C.CONV_QTY, 0) = 0 THEN 0 ELSE ISNULL(A.TIME_07, 0) / ISNULL(C.CONV_QTY, 0) END) ELSE ISNULL(A.TIME_07, 0) * ISNULL(C.CONV_QTY, 0) END) ELSE 0 END ) AS Formulation_P,

(CASE WHEN C.PROD_TYPE IN ( 'FF&P') THEN (CASE WHEN A.WORK_PRT_GB = '01' THEN (CASE WHEN ISNULL(C.CONV_QTY, 0) = 0 THEN 0 ELSE ISNULL(A.TIME_07, 0) / ISNULL(C.CONV_QTY, 0) END) ELSE ISNULL(A.TIME_07, 0) * ISNULL(C.CONV_QTY, 0) END) * 2
WHEN C.PROD_TYPE IN ('F', 'F&P') THEN (CASE WHEN A.WORK_PRT_GB = '01' THEN (CASE WHEN ISNULL(C.CONV_QTY, 0) = 0 THEN 0 ELSE ISNULL(A.TIME_07, 0) / ISNULL(C.CONV_QTY, 0) END) ELSE ISNULL(A.TIME_07, 0) * ISNULL(C.CONV_QTY, 0) END) ELSE 0 END ) AS Formulation_T

[abhijitnalekar]

Hi @rupaliqlik,

Please check below expressions.

if( match(C.PROD_TYPE,'FF&P')=1,
if(A.WORK_PRT_GB = '01',
if(coalesce(C.CONV_QTY, 0) = 0 , 0,coalesce(A.TIME_07, 0) / coalesce(C.CONV_QTY, 0)),coalesce(A.TIME_07, 0) * coalesce(C.CONV_QTY, 0))*2,
if(match(C.PROD_TYPE,'F', 'F&P')>1,
if(A.WORK_PRT_GB = '01',
if(coalesce(C.CONV_QTY, 0)=0,0,coalesce(A.TIME_07, 0) / coalesce(C.CONV_QTY, 0), coalesce(A.TIME_07, 0) * coalesce(C.CONV_QTY, 0)))
),0
) as Formulation_T 

////////////////////////////////

if(match(C.PROD_TYPE,'F&P', 'FF&P')>1,
if(A.WORK_PRT_GB = '01',
if(Coalesce(C.CONV_QTY, 0)=0, 0,Coalesce(A.TIME_07, 0) / Coalesce(C.CONV_QTY, 0)
),
Coalesce(A.TIME_07, 0) * Coalesce(C.CONV_QTY, 0) ),0
)
as Formulation_P

Hope this helps