Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI i have a sales orders which has many lines .
suppose one sales order has three line:
on line condition for three lines is first one 1 second one 0 and the third is 0 .
I want to mark the complete sales order as o if single record is zero.
Sales Order | %SalesItem | Delivery Items | Perfect.RightLocation | Perfect.OnTime | Perfect.Vivaldi_OnTime | Perfect.Vivaldi_RightLocation |
---|---|---|---|---|---|---|
0102765108 | 0102765108/000010 | 0503304453/000010 | 1 | 1 | 1 | 1 |
0102765108 | 0102765108/000020 | 0503304454/000020 | 1 | 0 | 1 | 1 |
0102765108 | 0102765108/000030 | 0503304454/000030 | 1 | 0 | 1 | 1 |
Expected Result .
Sales order Flag on time
0102765108 0
As two of the records in perefct.ontine is zero i want to make complete order as 0.
Thanks in advance
What is the output you want to see in this scenario? All 0's in the fields starting with Prefix Perfect ?
Yes Nagaraj,
expected output is zero for all if any of the line is zero
Sales Order | %SalesItem | Perfect.OnTime | ||||
---|---|---|---|---|---|---|
0102765108 | 0102765108/000010 | 0 | ||||
0102765108 | 0102765108/000020 | 0 | ||||
0102765108 | 0102765108/000030 | 0 |
Hi
Orders:
load * Inline [
OrderNo,ItemNo,TimeFlag
100,1,1
100,2,0
100,3,1
200,1,1
200,2,1
200,3,1
300,1,1
300,2,1
300,3,0
];
Left join
LOAD
OrderNo,
Count(OrderNo) as OnTimeCNT
Resident Orders
where TimeFlag = 1
Group by OrderNo,TimeFlag;
Left join
LOAD
OrderNo,
Count(OrderNo) as LateCNT
Resident Orders
where TimeFlag = 0
Group by OrderNo,TimeFlag;
FinalTabel:
load *,
if(IsNull(LateCNT),1,0) as PerfectOnTime
Resident Orders;
DROP Table Orders;
// You should have a table similar to this order no 200 all items on Time rest are not
Attached is the QVW file
HI Mohammed Actually here is my script . can you please help with this.
PerfectOrder:
LOAD *,
if(Flag_DeliveredinFull = 'Y' and Flag_OnTimeandLocation = 'Y' and Flag_DocumentationAccurate = 'Y' and Flag_PerfectCondition = 'Y', 1, 0) as [Perfect Order Score];
LOAD *,
if(Perfect.Vivaldi_RightItem = 1 and Perfect.Vivaldi_RightQty = 1 and [Perfect.SAP_RightItem] = 1 and Perfect.SAP_RightQty = 1, 'Y', 'N') as Flag_DeliveredinFull,
if(Perfect.Vivaldi_OnTime = 1 and Perfect.Vivaldi_RightLocation = 1 and [Perfect.RightLocation] = 1 and [Perfect.OnTime] = 1, 'Y', 'N') as Flag_OnTimeandLocation,
if(Perfect.Vivaldi_Shipping = 1 and Perfect.Vivaldi_Compliance = 1 and Perfect.Vivaldi_Payment = 1, 'Y', 'N') as Flag_DocumentationAccurate,
if(Perfect.Vivaldi_DamageFree = 1, 'Y', 'N') as Flag_PerfectCondition,
1 as OrderRecordCounter;
LOAD
[Sales Order]& [Delivery Items]&Delivery_Line_Item as Key,
Delivery_Line_Item as DLI,
[Delivery Items] as DI,
min(Vivaldi_RightItem) as Perfect.Vivaldi_RightItem,
min(Vivaldi_RightQty) as Perfect.Vivaldi_RightQty,
min(Vivaldi_OnTime) as Perfect.Vivaldi_OnTime,
min(Vivaldi_RightLocation) as Perfect.Vivaldi_RightLocation,
min(Vivaldi_Shipping) as Perfect.Vivaldi_Shipping,
min(Vivaldi_Compliance) as Perfect.Vivaldi_Compliance,
min(Vivaldi_Payment) as Perfect.Vivaldi_Payment,
min(Vivaldi_DamageFree) as Perfect.Vivaldi_DamageFree,
max([Requested Delivery Date]) as [Order Req. Dlv Date],
max([ShipmentDate-SCV]) as [Dlv Date (SCV)],
if(concat(DISTINCT [Sales Order Material], ',') = concat(DISTINCT [Delivery Material], ','), 1, 0) as [Perfect.SAP_RightItem],
if(sum([Sales Order Quantity]) = sum([Delivery Item Quantity]), 1, 0) as Perfect.SAP_RightQty,
if(len([ShipmentDate-SCV])='0', if(Date(Actual_GI_Date,'YYYY-MM-DD')=date(Planned_GI_Date,'YYYY-MM-DD') or NetWorkDays(Date(Actual_GI_Date,'YYYY-MM-DD'),date(Planned_GI_Date,'YYYY-MM-DD'))=1 or NetWorkDays(Date(Actual_GI_Date,'YYYY-MM-DD'),date(Planned_GI_Date,'YYYY-MM-DD'))=2 ,1,0), if( [ShipmentDate-SCV] <= Planned_Delivery_Date, 1, 0)) as [Perfect.OnTime],
if( max(numsum([SCV Record Exists]))=0 , 1, if((concat(DISTINCT [Zip-Order], ',') = concat(DISTINCT [Zip-SCV] , ',')) or [Zip-SCV Flag]=1, 1, 0)) as [Perfect.RightLocation]
RESIDENT sales_Orders
GROUP By [Sales Order],
[Zip-SCV Flag],
Actual_GI_Date,Planned_GI_Date,
Planned_Delivery_Date,
[ShipmentDate-SCV],
[Delivery Items],
Delivery_Line_Item;
now for this sales order if any of the perefcet on time is 0 i want to mark the complete order as o
Hi Naveen
Please provide with a sample Data on excel file to simulate this
HI Mohammed please find the attached sample data;
In the field key first 10 characters are sales order number,
So for one sales order number if any of the line record is zero complete thing should be zero.
Key | DLI | DI | Perfect.Vivaldi_Compliance | Perfect.Vivaldi_DamageFree | Perfect.Vivaldi_OnTime | Perfect.Vivaldi_Payment | Perfect.Vivaldi_RightItem | Perfect.Vivaldi_RightLocation | Perfect.Vivaldi_RightQty | Dlv Date (SCV) | Perfect.Vivaldi_Shipping | Perfect.OnTime | Perfect.RightLocation | Perfect.SAP_RightItem | Perfect.SAP_RightQty |
01026133790503181935/900001 | 0503181935/900001 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | ||
01026133790503194145/900001 | 0503194145/900001 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
01027651080503304453/000010 | 0503304453/000010 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
01027651080503304454/000020 | 0503304454/000020 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | ||
01027651080503304454/000030 | 0503304454/000030 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | ||
01027680020503309216/000010 | 0503309216/000010 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
01027680020503309216/000020 | 0503309216/000020 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
01027680020503309216/000040 | 0503309216/000040 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
01027680020503309216/000050 | 0503309216/000050 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
01027680020503309216/000060 | 0503309216/000060 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
01027680020503309217/000030 | 0503309217/000030 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 |
What is the Order No or Key Field in the above Table .. is it Key or DLI