Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
naveen341
Creator
Creator

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

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
01027651080102765108/0000100503304453/0000101111
01027651080102765108/0000200503304454/0000201011
01027651080102765108/0000300503304454/0000301011

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

9 Replies
vishsaggi
Champion III
Champion III

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

naveen341
Creator
Creator
Author

Yes Nagaraj,

expected output is zero for all if any of the line is zero

Sales Order%SalesItem

Perfect.OnTime
01027651080102765108/0000100
01027651080102765108/0000200
01027651080102765108/0000300
ziadm
Specialist
Specialist

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

T3.png

ziadm
Specialist
Specialist

Attached is the QVW file

naveen341
Creator
Creator
Author

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
Creator
Creator
Author

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

ziadm
Specialist
Specialist

Hi Naveen

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

naveen341
Creator
Creator
Author

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.

                

KeyDLIDIPerfect.Vivaldi_CompliancePerfect.Vivaldi_DamageFreePerfect.Vivaldi_OnTimePerfect.Vivaldi_PaymentPerfect.Vivaldi_RightItemPerfect.Vivaldi_RightLocationPerfect.Vivaldi_RightQtyDlv Date (SCV)Perfect.Vivaldi_ShippingPerfect.OnTimePerfect.RightLocationPerfect.SAP_RightItemPerfect.SAP_RightQty
01026133790503181935/900001 0503181935/9000011111111 10111
01026133790503194145/900001 0503194145/9000011111111 11111
01027651080503304453/000010 0503304453/0000101111111 11111
01027651080503304454/000020 0503304454/0000201111111 10111
01027651080503304454/000030 0503304454/0000301111111 10111
01027680020503309216/000010 0503309216/0000101111111 11111
01027680020503309216/000020 0503309216/0000201111111 11111
01027680020503309216/000040 0503309216/0000401111111 11111
01027680020503309216/000050 0503309216/0000501111111 11111
01027680020503309216/000060 0503309216/0000601111111 11111
01027680020503309217/000030 0503309217/0000301111111 10111
ziadm
Specialist
Specialist

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