Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor II

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

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

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)

Highlighted
atkinsow
Valued Contributor II

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
Valued Contributor II

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!