topic Re: Nested If to create a drop down selection in QlikView

Nested If to create a drop down selection

Fri, 31 May 2013 08:40:32 GMT

Hi there, i have the following problem with a nested IF statement that won t work in a combined selection.

the code is the following:

=if (conto=62 and (sotco=30 or sotco=3 or sotco=27 or sotco=32 or sotco=15),62,

if(conto=13 and (sotco=6 or sotco=1 or sotco=3), 13,

if((conto=38 and sotco=36),38,

if(conto=12 and (sotco=32 or sotco=60 or sotco=35),12,

if(conto=10 and (sotco=30 or sotco=29 or sotco=26 or sotco=14 or sotco=27),10,

if(conto=23 and (sotco=27 or sotco=19 or sotco=20 or sotco=21 or sotco=22 or sotco=28),23,

if(conto=6 and (sotco=23 or sotco=22 or sotco=5 or sotco=24 or sotco=3 or sotco=18 or sotco=21 or sotco=28 or sotco=29 or sotco=11 or sotco=2 or sotco=27),6)))))))

the expression is OK and it is used as a calculated field in a drop down selection object.

The two criteria are Conto (parent) and Sotco. My goal is to display for each conto selection only certain sotco values... which works fine if i pick one choice only

for instance is i pick uniquely conto 38 then i see only sotco 36.

however the problem is ... if i select 38 and another let's say 13 i see all combinations possible insteda of the correct one... so i see 38-13, 38-6, 38-1, ....

I tried a work around by creating a dummy field that would do the following (conto*10000)+sotco = conto0sotco and specifyng all the unique combinations... alas it does exaclty the same as above once i combine them it ignores the nesting and trasnforms everything in one big select all conto and all sotco specified...

Can anybody tell if i am doing something wrong ?

thanks!!

Re: Nested If to create a drop down selection

Gysbert_Wassenaar — Fri, 31 May 2013 08:49:06 GMT

If you're using conto as dimension in your chart replace it with the nested if as a calculated dimension. If that's not the problem please create a sample document. I'm not very good at mind reading yet.

Re: Nested If to create a drop down selection

Fri, 31 May 2013 09:04:23 GMT

Hi Gysbert,

thanks for picking this topic up i don't expect you to read my mind... but i can t post the file either... since it contains sesitive financial data and i can't break the company confidentiality on this.

however i took screenshots to help you better understand my question.

So the expression is a calculated dimension in a drop down selection object. First screenshot shows that.

Second screenshot shows that the selections are all active, third screenshot shows what i don t want to see ( a combo of conto and sotco that should not be satisfied by the formula).

I ll see if i can build a dummy file with the same logic for you, but if you spot anything striking please let me know...thanks!

Re: Nested If to create a drop down selection

Fri, 31 May 2013 09:19:00 GMT

added a clean sample

to see the problem select all the conditions together and look at conto 38

it has all the sotco instead of only sotco 36

Re: Nested If to create a drop down selection

Gysbert_Wassenaar — Fri, 31 May 2013 09:24:30 GMT

As I said, replace conto with the nested if statement in your chart. See attached example.

Re: Nested If to create a drop down selection

Fri, 31 May 2013 09:30:49 GMT

right ... this would build the condition in the chart itself... i know that works... but i can t make the chart dynamic this way .. i.e. no selection equals all data....

I can live with this, just wanted to be sure.