Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
settu_periasamy
Master III
Master III

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

jagan
Luminary Alumni
Luminary Alumni

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
Author

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
Author

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'

settu_periasamy
Master III
Master III

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

jagan
Luminary Alumni
Luminary Alumni

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.

PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

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
Author

Max,

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