Count late delivery type Stop only if pickup Stop was On-Time

[JustinDallas]

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.

[sunny_talwar · Accepted solution]

Try this expression

=Count({<WasLate = {'Y'}, OrderNumber = E({<Action = {'Pickup'}, WasLate = {'Y'}>})>}StopNumber)

[Anonymous]

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;

[JustinDallas]

I had a feeling it involved P() and E(), I just wasn't sure how it would work with the data model.  Thanks!