Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Multiple If Statements

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 

15 Replies

Re: Multiple If Statements

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

MVP
MVP

Re: Multiple If Statements

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

OR

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.

Not applicable

Re: Multiple If Statements

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

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]>=1 and [Qty]>=1,'Instock') as Status 

Not applicable

Re: Multiple If Statements

Jagan,

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

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]>=1 and [Qty]>=1,'Instock') as Status 

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

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

OR

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

MVP
MVP

Re: Multiple If Statements

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

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

Regards,
Prashant Sangle
Not applicable

Re: Multiple If Statements

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.

Not applicable

Re: Multiple If Statements

Max,

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

Community Browser