topic Re: IF Statement in Qlikview in QlikView

IF Statement in Qlikview

Thu, 04 Apr 2013 09:31:21 GMT

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

Re: IF Statement in Qlikview

Thu, 04 Apr 2013 10:17:42 GMT

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

Re: IF Statement in Qlikview

swuehl — Thu, 04 Apr 2013 10:20:55 GMT

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

Re: IF Statement in Qlikview

Thu, 04 Apr 2013 11:56:23 GMT

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)

Re: IF Statement in Qlikview

Thu, 04 Apr 2013 13:26:13 GMT

Are u trying this in a load statement?

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

Re: IF Statement in Qlikview

Thu, 04 Apr 2013 13:53:51 GMT

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

Re: IF Statement in Qlikview

Thu, 04 Apr 2013 14:35:23 GMT

It works!

Thank you Angad!!!

Woohoo

Re: IF Statement in Qlikview

Thu, 04 Apr 2013 14:36:35 GMT

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

Re: IF Statement in Qlikview

Fri, 05 Apr 2013 05:16:59 GMT

Thanks Juliette,

Can you please mark your question as answered.

Thanks,

Angad