Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jeanjordaan007
New Contributor

Check deliveries are in a sequence.

  1. Hi.

I need to calculate if a delivery truck follows the planned delivery route. Below is an example of the data. In the sample data we have the following fields.

    [Shipment No],  - This is the number for all the deliveries on the truck. A single shipment will have many Deliveries Numbers.
[Stop Sequence], - This is the planned sequence for each shipment.
[Delivery No], - Each delivery number is unique within a shipment.
[Customer Arrival Date],
[Customer Arrival Time]  - This is the actual delivery time. From this it is possible to derive the actual delivery sequence.

The requirement is to see if the actual delivery sequence matches up with the planned delivery sequence. A delivery is seen as “in sequence” when the following conditions  are true.

  1. 1. If the planned delivery number is 1st in the Stop Sequence AND number 1 in the Actual Delivery sequence.
  2. 2. If the previous delivery number in the actual deliver sequence is equal to the Previous Planned Delivery number.

Here is an example of how it should be .

Some of the stops are at malls. So the stop sequence for a couple of deliveries are the same but with different delivery numbers. However when calculating the actual based on the Customer delivery time the times are not always the same for that one stop. I have been told that I can use the MIN time as the customer Delivery time for that site.

In eed help in checking if they are in sequence or not.

Shipment Number

Delivery Number

Stop Sequence

Customer Delivery Time

actual Sequence

In Sequense

Notes

SH001

DEL001

1

1

Yes

Actual Sequence 1 and Planned is 1

SH001

DEL002

1

1

Yes

Actual Sequence 1 and Planned is 1

SH001

DEL003

1

1

Yes

Actual Sequence 1 and Planned is 1

SH001

DEL004

2

12

No

The previous planned delivery number for the actual delivery number is not matching. The Planned previsous delivery number (11) is DEL18, However the Actual previos delivery number for this one is (DEL001 and DEL003 and DEL002)

SH001

DEL005

3

3

NO

Even though the actual and the planned  sequence is the same, we are interested in the if the sequence between the actual previsou delivery number and the planned previsou is the same. This is Actual number 3 in the sequence. The Planned previous based on the planned current is DEL004, The Actual Sequence (2) delivery number is DEL009.

SH001

DEL006

4

4

Yes

SH001

DEL007

5

5

Yes

SH001

DEL008

6

6

Yes

SH001

DEL009

7

2

No

SH001

DEL010

8

8

No

SH001

DEL011

8

8

No

SH001

DEL012

8

8

No

SH001

DEL013

8

8

No

SH001

DEL014

8

8

No

SH001

DEL015

9

9

Yes

SH001

DEL016

10

10

Yes

SH001

DEL017

10

10

Yes

SH001

DEL018

11

11

Yes

SH001

DEL019

12

7

No

Tags (2)
Community Browser