Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Jsobrinho77
Creator
Creator

Alternative Switch Case Qlik Script

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 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

 

1 Reply
anat
Master
Master

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
))))))))))))))))))