topic Re: Case statement with in clause in load editor in QlikView

Case statement with in clause in load editor

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

In my load script I have

SELECT JOB_NUM, FIELD_2, FIELD_3, FIELD_4,

CASE WHEN JOB_NUM IN ('JOB_1', 'JOB_2', 'JOB_3') THEN 1 ELSE 0 END AS FLAG_1,

CASE WHEN JOB_NUM IN ('JOB_4', 'JOB_5', 'JOB_6') THEN 1 ELSE 0 END AS FLAG_2,

CASE WHEN JOB_NUM IN ('JOB_7, 'JOB_8', 'JOB_9') THEN 1 ELSE 0 END AS FLAG_3,

CASE WHEN JOB_NUM IN ('JOB_1', 'JOB_4', 'JOB_7') THEN 'MONDAY_JOBS'

WHEN JOB_NUM IN ('JOB_2', 'JOB_5', 'JOB_8') THEN 'TUESDAY_JOBS'

WHEN JOB_NUM IN ('JOB_3', 'JOB_6', 'JOB_9') THEN 'WEDNESDAY_JOBS'

ELSE 0 END AS JOB_DAY

FROM JOB_CONTROL.JOB_CONTROL

I then save this as a temporary table.

This runs and works well when I comment out the red text but errors when I include the text in red.

Any ideas?

Nicole-Smith — Wed, 24 Oct 2018 15:40:58 GMT

It might have something to do with casting. Does it run if you change it to the following?

CASE WHEN JOB_NUM IN ('JOB_1', 'JOB_4', 'JOB_7') THEN 'MONDAY_JOBS'

WHEN JOB_NUM IN ('JOB_2', 'JOB_5', 'JOB_8') THEN 'TUESDAY_JOBS'

WHEN JOB_NUM IN ('JOB_3', 'JOB_6', 'JOB_9') THEN 'WEDNESDAY_JOBS'

ELSE CAST(0 AS VARCHAR) END AS JOB_DAY

Anonymous — Thu, 25 Oct 2018 08:10:35 GMT

It doesn't seem to like that either! Thanks for your help though

Anonymous — Thu, 25 Oct 2018 15:45:20 GMT

Solved by creating the column when I loaded data from the table back in instead