Multiple If Statements

[#1]

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 

[settu_periasamy]

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

[#3]

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') 

[settu_periasamy]

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?

[maxgro]

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'

))))

[#6]

Settu,

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

[pljsoftware]

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