IF Statement in Qlikview

[#1]

Hey There,

I have an issue with my IF statement.

it is a dual if, where im asking it to verfy the period, and then do a calculation.

If I run each of the IF statements on their own, I get results, but when combining them, I get a - result

Please can you assist with the syntax.

if(Period < 201302,

if(ABC='ABC1' and [Flag]='X' [No of Boxes] * 15,

if(Period>= 201302,

if(ABC='ABC1' and [Flag]='X' [No of Boxes] * 20))),0)*1.14

[#2]

Hello,

Can you please try the below statement :

if(Period < 201302,

if(ABC='ABC1' and [Flag]='X',[No of Boxes] * 15,

if(Period>= 201302,

if(ABC='ABC1' and [Flag]='X',[No of Boxes] * 20)))*1.14 ,0)

It will return 0, if no condition is matched.

Please let me know if this was helpful.

thanks,

angad

[swuehl]

Juliette,

maybe it's just a typo in your post, but I think you are missing commata before [No of boxes] to start your THEN branch in your if-statements:

if(Period < 201302,

if(ABC='ABC1' and [Flag]='X', [No of Boxes] * 15,

if(Period>= 201302,

if(ABC='ABC1' and [Flag]='X', [No of Boxes] * 20))),0)*1.14

Then, you are not defining an ELSE branch for every possible combination of values, so this might lead to a '-' result.

Finally, you are not using an aggregation function, so your expression will only work if you get unambiguous values for ABC, Flag, No of Boxes, Period in the context you are evaluating.

Hope this helps,

Stefan

[#4]

Hi Stephan,

I was actually putting in an example of the IF statement.

Here is the actual IF statement which is not working.

I have tried everyones examples, and i cannot get it to work.

thank you

if(Period < 201302,

if(NCS='NCSNP' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 12,

if(NCS='NCSNP' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,

if(NCS='NCS PHY 500' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 12,

if(NCS='NCS PHY 500' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,

if(NCS='NCS PHY 300' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 12,

if(NCS='NCS PHY 300' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,

if(Period>= 201302,

if(NCS='NCSNP' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 20,

if(NCS='NCSNP' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,

if(NCS='NCS PHY 500' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 20,

if(NCS='NCS PHY 500' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,

if(NCS='NCS PHY 300' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 20,

if(NCS='NCS PHY 300' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5)))))))))))))*1.14,0)

[#5]

Are u trying this in a load statement?

If u r doing in load statement, U should get the result, and performance will increase.

[#6 · Accepted solution]

Hello Juliette,

Can you try the below query please :

if(Period < 201302,

          if((NCS='NCSNP'or NCS = 'NCS PHY 500' or NCS='NCS PHY 300') and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX' ,[No of Rx] * 12,

                    if((NCS='NCSNP' or NCS='NCS PHY 500' or NCS='NCS PHY 300') and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,

if(Period>= 201302,

          if((NCS='NCSNP' or NCS='NCS PHY 500' or NCS='NCS PHY 300') and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 20,

                    if((NCS='NCSNP' or NCS='NCS PHY 500' or NCS='NCS PHY 300) and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5)))))*1.14,0)

Please let me know if this was helpful.

Thanks,

Angad

[#7]

It works!

Thank you Angad!!!

Woohoo 

[#8]

No, Im not trying in a load statement, as i am using my dimensions which i calculate.

thanks for the info though, i will try it like that later.

thanks for the help y'all

[#9]

Thanks Juliette,

Can you please mark your question as answered.

Thanks,

Angad