Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinDallas
Specialist III
Specialist III

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

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.

1 Solution

Accepted Solutions
sunny_talwar

Try this expression

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

View solution in original post

3 Replies
sunny_talwar

Try this expression

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

Anonymous
Not applicable

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
Specialist III
Specialist III
Author

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