Alternative Switch Case Qlik Script

Jsobrinho77 — Fri, 11 Feb 2022 00:14:59 GMT

Hi guys,

I have a switch case statement from SQL with a lot of conditionals, has one alternative into Qlikview script? Do I need to create a lot of apply map? Any ideas to create inside script the logic?

CASE
WHEN CODE IN ('A','B', 'C', 'D', 'E', 'F', 'G', 'H') THEN 2

WHEN LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'A' THEN 3

WHEN LENGTH (CODE) = 9 AND CODE_TEXT IN ('Region') THEN 5
WHEN LENGTH (CODE) = 9 AND LEFT(CODE_TEXT, 2) = 'AA' THEN 6
WHEN LEFT(CODE,3) = 'XC' AND (CODE_TEXT not in ('X') AND RIGHT(CODE_TEXT, 7 ) <> 'BB') THEN 5
WHEN LEFT(CODE,3) = 'XC' AND RIGHT(CODE_TEXT, 7 ) = 'BB' THEN 4
WHEN LEFT(CODE,3) = 'XC' AND CODE_TEXT in ('X') THEN 3
WHEN LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'DD' THEN 3
WHEN LENGTH (CODE) = 12 AND LEFT(RIGHT(CODE,6),3) = 'DD' THEN 4
WHEN LENGTH (CODE) = 5 AND LEFT (CODE,2) in ('23','24','25') THEN 4
WHEN LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'XXX' THEN 3
WHEN LENGTH (CODE) > 6 AND LEFT(CODE, 3) = 'NN' THEN 4
WHEN LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'HJL' THEN 3
WHEN LENGTH (CODE) > 6 AND LEFT(CODE, 3) = 'OP' THEN 4
WHEN LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'PP' THEN 3

WHEN CODE in ('A1','A2', 'A3') THEN 5
WHEN CODE in ('B1','B3') THEN 4
WHEN CODE in ('C1','C3', 'C4') THEN 4
ELSE 0
END AS LEVEL

I would like to create a field,LEVEL, inside my load script like

LOAD

CASE CODE
WHEN CODE IN ('A','B', 'C', 'D', 'E', 'F', 'G', 'H') THEN 2

WHEN LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'A' THEN 3

WHEN CODE in ('A1','A2', 'A3') THEN 5
WHEN CODE in ('B1','B3') THEN 4
WHEN CODE in ('C1','C3', 'C4') THEN 4
ELSE 0
END AS LEVEL

Re: Alternative Switch Case Qlik Script

anat — Fri, 11 Feb 2022 06:06:06 GMT

use IF statement ,

if(CODE IN ('A','B', 'C', 'D', 'E', 'F', 'G', 'H') , 2,
if( LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'A' , 3,
if( LENGTH (CODE) = 9 AND CODE_TEXT IN ('Region') , 5,
if( LENGTH (CODE) = 9 AND LEFT(CODE_TEXT, 2) = 'AA' , 6,
if( LEFT(CODE,3) = 'XC' AND (CODE_TEXT not in ('X') AND RIGHT(CODE_TEXT, 7 ) <> 'BB') , 5,
if( LEFT(CODE,3) = 'XC' AND RIGHT(CODE_TEXT, 7 ) = 'BB' , 4,
if( LEFT(CODE,3) = 'XC' AND CODE_TEXT in ('X') , 3,
if( LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'DD' , 3,
if( LENGTH (CODE) = 12 AND LEFT(RIGHT(CODE,6),3) = 'DD' , 4,
if( LENGTH (CODE) = 5 AND LEFT (CODE,2) in ('23','24','25') , 4,
if( LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'XXX' , 3,
if( LENGTH (CODE) > 6 AND LEFT(CODE, 3) = 'NN' , 4,
if( LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'HJL' , 3,
if( LENGTH (CODE) > 6 AND LEFT(CODE, 3) = 'OP' , 4,
if( LENGTH (CODE) = 6 AND LEFT(CODE, 3) = 'PP' , 3,
if( CODE in ('A1','A2', 'A3') , 5,
if( CODE in ('B1','B3') , 4,
if( CODE in ('C1','C3', 'C4') , 4, 0
))))))))))))))))))

topic Alternative Switch Case Qlik Script in QlikView

Alternative Switch Case Qlik Script

Re: Alternative Switch Case Qlik Script