I want to mark complete sales order as 0 even if single record is zero

[naveen341]

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

[vishsaggi]

What is the output you want to see in this scenario? All 0's in the fields starting with Prefix Perfect ?

[naveen341]

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

[ziadm]

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

[ziadm]

Attached is the QVW file

[naveen341]

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;

[naveen341]

now for this sales order if any of the perefcet on time is 0 i want to mark the complete order as o

[ziadm]

Hi Naveen

Please provide with a sample Data on excel file to simulate this

[naveen341]

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

[ziadm]

What is the Order No or Key Field in the above Table .. is it Key or DLI