Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
)
Thanks,
But I want to convert this in qliksense script.
Coalesce function will not work here.
Coalesce function will work
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
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