Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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')
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?
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'
))))
Settu,
All good now seems to be working ok. Thanks for your help.
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