Counter problem with Sales_Order_Detail vs Sales_Order

[#1]

I am tring to set up a counter and group of buckets to track late shipments. The ship date and due date reside in the sales_order_detail table.

I set up the following during my load:

if( (ShipDate-DueDate) >0,(ShipDate-DueDate),0) AS DaysShipLate,

if

(ShipDate - DueDate >0 ,1,0) AS LateShipCounter,

The DaysShipLate works great with Class() to set up buckets but when I try to sum the LateShipCounter, I get a wrong number because the oders have multiple lines from the order detail. I know I need to use the order ID only for this but I get a LateShipCounter of 1 for each line item in the order. How can I get only one counter for each order ? I have tried aggr, leftjoins, contatenate (possibly not correctly) without sucess. Am I moving in the right direction? Any help would be appreciated.

Thanks,

Dennis

[#2 · Accepted solution]

4 Hours Sleep and the following seem to have solved my problem.

INNER

JOIN

LOAD

SalesOrderID

,

sum

(Distinct if(ShipDate - DueDate >0 ,1,0) ) AS LateShipCounter

RESIDENT

OrderDetail

GROUP

BY SalesOrderID;

Never mind unless oyu have a better way to solve this.

Thanks,

Dennis

[#3]

4 Hours Sleep and the following seem to have solved my problem.

INNER

JOIN

LOAD

SalesOrderID

,

sum

(Distinct if(ShipDate - DueDate >0 ,1,0) ) AS LateShipCounter

RESIDENT

OrderDetail

GROUP

BY SalesOrderID;

Never mind unless oyu have a better way to solve this.

Thanks,

Dennis