topic Re: Multiple If Statements in QlikView

Multiple If Statements

Thu, 12 Nov 2015 00:00:28 GMT

Hi All,

Can someone please assist with the following load script. I am trying to get the 3 statuses loaded from the one If statement. What am I missing.

If([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')
and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')
and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'Instock') as Status

Re: Multiple If Statements

settu_periasamy — Thu, 12 Nov 2015 03:58:10 GMT

Hi,

If statement with or should be like [MRStatus]='ZP' or [MRStatus]='ZD' instead [MRStatus]='ZP'or'ZD'

So, try the below

If([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0,

if([Qty]=0,'New',

if([Qty]>=1,'OOS',

if([Qty]>=1,'Instock','Others')))) as Status

You can use the Match instead of Nested if Statement

Re: Multiple If Statements

jagan — Thu, 12 Nov 2015 04:58:39 GMT

Hi,

Try like this

If([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')

and if([MRStatus]='ZP'or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')

and if([MRStatus]='ZP'or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'Instock') as Status

If(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')

and if(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')

and if(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'Instock') as Status

Also check your second and third if condition is same, so you will get only two values New & OOS only.

Regards

Jagan.

Re: Multiple If Statements

Thu, 12 Nov 2015 05:36:24 GMT

Settu,

This creates the Statuses but they are not correct to the criteria. Also I made an error in the third one. Unrestrict should be >=1

Re: Multiple If Statements

Thu, 12 Nov 2015 05:40:08 GMT

Jagan,

These create the Statuses I want, but aren't correct to the criteria. Also the third item was incorrect should be as follows.

I also need to added that if MRStatus is not ZP or ZD regardless of the other criteria it is a 'Deleted Item'

Re: Multiple If Statements

settu_periasamy — Thu, 12 Nov 2015 06:01:58 GMT

Hi Kerry,

Try this..

If([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0,

if( [Unrestrict]=0 and [Qty]=0,'New',

if([Unrestrict]=0 and [Qty]>=1,'OOS',

if([Unrestrict]>=1 and [Qty]>=1,'Instock', 'Deleted Item')))) as Status

If([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New',

if([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS',

if([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]>=1 and [Qty]>=1,'Instock', 'Deleted Item'))) as Status

Re: Multiple If Statements

jagan — Thu, 12 Nov 2015 06:07:03 GMT

Try this

If(Match([MRStatus], 'ZP', 'ZD') AND (Not Match([MRStatus], 'ZP', 'ZD') OR [other criteria] = 'Deleted Item') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')

and if(Match([MRStatus], 'ZP', 'ZD') AND (Not Match([MRStatus], 'ZP', 'ZD') OR [other criteria] = 'Deleted Item') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')

and if(Match([MRStatus], 'ZP', 'ZD') AND (Not Match([MRStatus], 'ZP', 'ZD') OR [other criteria] = 'Deleted Item') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]>=1 and [Qty]>=1,'Instock') as Status

Regards,

Jagan.

Re: Multiple If Statements

PrashantSangle — Thu, 12 Nov 2015 06:15:13 GMT

Hi,

You required little modification in Jagan's Solutions

Try below

If(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New',

if(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS',

if(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]>=1 and [Qty]>=1,'Instock','Deleted Item')))

Regards

Re: Multiple If Statements

Fri, 13 Nov 2015 00:05:03 GMT

Settu,

The second one creates the status but it ignores the criteria. So for new and OOS I still get values when there should be no Unrestrict amount.

Re: Multiple If Statements

Fri, 13 Nov 2015 00:05:58 GMT

Max,

This ignores the criteria so if I select New or OOs it still gives me values in Unrestrict instead of being zero.

Re: Multiple If Statements

settu_periasamy — Fri, 13 Nov 2015 04:20:19 GMT

Hi,

Do you have the Null values in your Unrestrict Field, if so, you need to trim it.

Try the below expression..

If([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and Len(Trim([Unrestrict]))>0 and [Qty]=0,'New',

if([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and Len(Trim([Unrestrict]))>0 and [Qty]>=1,'OOS',

if([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]>=1 and [Qty]>=1,'Instock', 'Deleted Item'))) as Status

Re: Multiple If Statements

Fri, 13 Nov 2015 05:39:13 GMT

Settu,

I have the If statements in 4 different fields in a report and they work ok see below. I just want these in the load as one statement.

Field 1: =If(([MR Status]='ZP' or [MR Status]='ZD') AND Unrestrict-OpenQty<=0 and Unrestrict=0 and sum(Qty)=0,'New')

Field 2: =If(([MR Status]='ZP' or [MR Status]='ZD') AND Unrestrict-OpenQty<=0 and Unrestrict=0 and sum(Qty)>=1,'OOS')

Field 3: =If(([MRP Status]='ZP' or [MR Status]='ZD') AND sum(Unrestrict)>=1 and sum(Qty)>=1,'InStock')

Field 4: =If(not([MR Status]='ZP' or [MR Status]='ZD'),'DeletedItem')

Re: Multiple If Statements

settu_periasamy — Fri, 13 Nov 2015 07:48:59 GMT

Hi Kerry,

check the below one..

=If(([MR Status]='ZP' or [MR Status]='ZD') AND sum(Unrestrict)-Sum(OpenQty)<=0 and Sum(Unrestrict)=0 and sum(Qty)=0,'New',

If(([MR Status]='ZP' or [MR Status]='ZD') AND sum(Unrestrict)-Sum(OpenQty)<=0 and Sum(Unrestrict)=0 and sum(Qty)>=1,'OOS',

If(([MRP Status]='ZP' or [MR Status]='ZD') AND sum(Unrestrict)>=1 and sum(Qty)>=1,'InStock',

If(not([MR Status]='ZP' or [MR Status]='ZD'),'DeletedItem'))))

If it is not working, Can you Post the sample qvw?

Re: Multiple If Statements

maxgro — Fri, 13 Nov 2015 08:25:06 GMT

you said

I have the If statements in 4 different fields in a report and they work ok see below. I just want these in the load as one statement.

Field 1: =If(([MR Status]='ZP' or [MR Status]='ZD') AND Unrestrict-OpenQty<=0 and Unrestrict=0 andsum(Qty)=0,'New')

Field 2: =If(([MR Status]='ZP' or [MR Status]='ZD') AND Unrestrict-OpenQty<=0 and Unrestrict=0 andsum(Qty)>=1,'OOS')

Field 3: =If(([MRP Status]='ZP' or [MR Status]='ZD') AND sum(Unrestrict)>=1 and sum(Qty)>=1,'InStock')

Field 4: =If(not([MR Status]='ZP' or [MR Status]='ZD'),'DeletedItem')

if they work ok

you just need to remove the sum and add some "," and ")"

if( (condition field 1 without sum), 'New',

if( (condition field 2 without sum), 'OOS',

if( (condition field 3 without sum), 'InStock',

if( (condition field 4 without sum), 'DeletedItem'

))))

Re: Multiple If Statements

Mon, 16 Nov 2015 05:17:24 GMT

Settu,

All good now seems to be working ok. Thanks for your help.

Re: Multiple If Statements

pljsoftware — Tue, 17 Nov 2015 17:02:30 GMT

Hi Kerry,

your code:

If([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')
and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')
and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'Instock') as Status

my code:

If(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty) <= 0 AND Unrestrict = 0

,If([Qty]=0, 'New'

,If([Qty] >= 1, 'OOS'

,If([Qty]>=1, 'Instock')

))) as Status

the result is exactly what you expect but I want you note [Qty]>=1

I think that it is incorrect because the last two conditions are the same but you want to show 2 different results (OSS, Instock).

Best Regards

Luca Jonathan Panetta