Convert case when in nested if in qliksense - Qlik Community

rupaliqlik · ‎2021-11-03

Hi Folks,

I want to join 2 tables and create customized fields.

SELECT
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C01')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE1 AS VARCHAR(10)) ,'0') END),'0') as DOWM_TIME_VALUE1

FROM COMM A LEFT OUTER JOIN P_DOWN_TIME_MNG B
ON A.COMM_DTL_CD = B.DOWN_CD1

How to calculate DOWM_TIME_VALUE1

I am writing query like this in qliksense.->

Load IF(COMM_DTL_CD= 'R01' ,
If(COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C01',DOWM_TIME_VALUE1,0)) as DOWM_TIME_VALUE1

@abhijitnalekar

Thanks,

rupali

abhijitnalekar · ‎2021-11-08

Hi @rupaliqlik ,

Can you please share full script?

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!

rupaliqlik · ‎2021-11-08

SELECT ISNULL(B.ROW_SEQ, ROW_NUMBER() OVER (ORDER BY A.COMM_CD)) ROW_SEQ

, A.COMM_DTL_CD DOWN_TIME_CD

, A.COMM_DTL_NM DOWN_TIME_NM

, (CASE A.COMM_DTL_NM

WHEN 'shift' THEN B.SHIFT_CD

ELSE A.COMM_DTL_NM2 END) SUB_NM

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C01')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE1 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE1

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C02')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE2 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE2

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C03')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE3 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE3

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C04')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE4 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE4

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C05')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE5 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE5

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C06')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE6 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE6

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C07')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE7 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE7

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C08')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE8 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE8

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C09')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE9 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE9

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C10')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE10 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE10

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C11')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE11 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE11

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C12')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE12 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE12

, ISNULL((CASE A.COMM_DTL_CD

WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C13')

ELSE ISNULL( CAST(B.DOWM_TIME_VALUE13 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE13

FROM COMM A LEFT OUTER JOIN

P_DOWN_TIME_MNG B

ON A.COMM_DTL_CD = B.DOWN_CD1

AND B.WORK_PRT_GB = '02' -- Formulation Solid or Liquid or Packing

AND B.LINE_CD = 'WG3F' -- Line

AND B.AGI_CODE = '228797' -- Product

AND B.SHIFT_CD = '2' -- Shift

AND B.WORK_DATE = '2021-03-02' -- Date

WHERE A.COMM_CD = 'DTM'