topic Converting Nested IFs in QlikView

Converting Nested IFs

Anonymous — Thu, 14 Oct 2010 23:18:13 GMT

I am converting an old Hyperion query to QlikView and need to convert the following code to its SWITCH/CASE equivalent in the load script. But I'm not sure how to handle the substrings. Can anyone offer some help?

if (Rbu == 700308 || Rbu == 700309 || Rbu == 700310) {"2. SHF" } else { if ( Substr ( Rbu, 4, 1 ) == "3") {"1. FBU" } else { if (Substr ( Rbu, 4, 1 ) == "4" ) {"3. VBU" } else { if (Substr ( Rbu, 4, 1 ) == "7" ) {"2. ABU" } else { if ( Substr ( Rbu, 4, 1 ) == "6") {"3. NBU" } else {"Missing" }}}}}

Converting Nested IFs

pover — Thu, 14 Oct 2010 23:49:45 GMT

I have never used the switch/case in the script and I'm not sure if Rbu is the column name or a variable, but would something like the following work:

Switch mid(Rbu,4,1)

Case 3

...

Case 4

....

etc.

Regards.

Converting Nested IFs

Anonymous — Fri, 15 Oct 2010 02:13:02 GMT

It looks like Switch statements are not supported within a Load statement. The following code worked. My thanks to Stefan Walther at QlikBlog.at for his explanation and his excellent Nested If tool.

http://www.qlikblog.at/464/tool-creating-nested-ifstatements/

RBU,
if(mid(RBU,4,1)='3','1. FBU',
if(mid(RBU,4,1)='4','4. VBU',
if(mid(RBU,4,1)='7','2. ABU',
if(mid(RBU,4,1)='6','3. NBU',
if(RBU='700308' or RBU='700309' or RBU='700310','2. SHF','Missing')))))
as "Revised_RBU",

Converting Nested IFs

Sat, 16 Oct 2010 09:07:18 GMT

I think you conversion isn't 100% right!

Your original statement was:

The first step is to check if Rbu equals the values 700308, 700309 or 700310. If this isn't the case you check the 4th number of the Rbu string.

With this statement:

if(mid(RBU,4,1)='3','1. FBU',
if(mid(RBU,4,1)='4','4. VBU',
if(mid(RBU,4,1)='7','2. ABU',
if(mid(RBU,4,1)='6','3. NBU',
if(RBU='700308' or RBU='700309' or RBU='700310','2. SHF','Missing')))))
as "Revised_RBU",

you check first the 4th number! Because 700308 = mid(RBU,4,1)='3' you'll get 'FBU' instead of 'SHF' !!!

Converting Nested IFs

Anonymous — Sun, 17 Oct 2010 03:54:52 GMT

You are correct. I changed it right after I noticed it was wrong. Thanks!