Converting Nested IFs

[Anonymous]

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

[pover]

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.

[Anonymous · Accepted solution]

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

[#4]

I think you conversion isn't 100% right!

Your original statement was:

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

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

[Anonymous]

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