Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
JustinDallas
Not applicable

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
Not applicable

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

Try this expression

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

3 Replies
sunny_talwar
Not applicable

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

Try this expression

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

atkinsow
Not applicable

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

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
Not applicable

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

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