Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have a problem where I am attempting to answer the following question:
"Count the late delivery stops, ONLY when the associated pickup stop was on-time"
For instance, here is a sample script:
Stops:
LOAD * Inline
[
'OrderNumber', 'StopNumber', 'Action', 'WasLate'
1, 101, Pickup, Y
1, 102, Delivery, N
1, 103, Delivery, Y
2, 201, Pickup, N
2, 202, Delivery, Y
2, 203, Delivery, Y
3, 301, Pickup, N
3, 302, Delivery, Y
3, 303, Delivery, N
]
In this instance, a following Table would have the following results.
Order Number | Late Stops
1 , 0
2 , 2
3 , 1
The reason the OrderNumber "1" has a LateStop count of 0 is because the Pickup was late, so we don't count those Late Deliveries (Stop Number=103).
In SQL I would say something like this:
SELECT COUNT(*) FROM Stops s
WHERE s.WasLate = 'Y'
AND s.OrderNumber NOT IN
(
SELECT OrderNumber FROM Stops late
WHERE late.Action = 'Pickup'
AND late.WasLate = 'Y'
)
Any help on this is greatly appreciated.
Try this expression
=Count({<WasLate = {'Y'}, OrderNumber = E({<Action = {'Pickup'}, WasLate = {'Y'}>})>}StopNumber)
Try this expression
=Count({<WasLate = {'Y'}, OrderNumber = E({<Action = {'Pickup'}, WasLate = {'Y'}>})>}StopNumber)
Or do something like this in the script:
MapPUStops:
Mapping Load
OrderNumber,
WasLate
Resident Stops
where Action='Pickup'
;
noconcatenate
StopsRestate:
Load
OrderNumber,
StopNumber,
Action,
if(Action='Pickup','WasLate,
if(Action='Delivery' and PULate='Y','N',WasLate)) as WasLate
;
Load *,
ApplyMap('MapPUStops',OrderNumber,'N') as PULate
Resident Stops
Drop Table Stops;
I had a feeling it involved P() and E(), I just wasn't sure how it would work with the data model. Thanks!