Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jamelmessaoud
Contributor

Delivery/Collection fields

Hi guys,

This is really difficult to explain but here goes....

I have some data which is loaded like this

       

Row No.Datepostcodecollectioncollection_postcodedeliverydelivery_postcode
123/02/2018WS11No No
223/02/2018WV10No No
323/02/2018WV11No No
423/02/2018WV12No No
523/02/2018WS2No No
623/02/2018WS5No No
723/02/2018WS10No No
823/02/2018B71No No
923/02/2018B70No No
1023/02/2018B69No No
1123/02/2018DY4No No
1223/02/2018DY1No No
1323/02/2018DY2No No
1423/02/2018DY5No YesDY5
1523/02/2018WV14No No
1623/02/2018WV13No No
1723/02/2018ST19No No
1823/02/2018ST18No No
1923/02/2018ST17No No
2023/02/2018ST15No No
2123/02/2018ST4No No
2223/02/2018ST5No No
2323/02/2018ST3No No
2423/02/2018ST11No No
2523/02/2018ST9No No
2623/02/2018ST13No YesST13
2723/02/2018ST2No No
2823/02/2018ST6No No
2923/02/2018ST1No No
3023/02/2018ST7No No
3123/02/2018CW2No No
3223/02/2018CW5No No
3323/02/2018CW6No No
3423/02/2018CH3YesCH3No
3523/02/2018CW8No No
3623/02/2018WA4No No
3723/02/2018WA16No No
3823/02/2018WA13No No
3923/02/2018WA1No No
4023/02/2018WA2No No
4123/02/2018WA3No No
4223/02/2018WA12No No
4323/02/2018WN4No No
4423/02/2018WN5No No
4523/02/2018WN8No YesWN8
4623/02/2018WA11No No
4723/02/2018WA9No No
4823/02/2018WA10YesWA10No
4923/02/2018L35No No
5023/02/2018WA8No No
5123/02/2018WA7No YesWA7
5223/02/2018WA6No No
5323/02/2018CH2No No
5424/02/2018CH3YesCH3No
5526/02/2018CH3YesCH3No
5626/02/2018CW6No No
5726/02/2018CW5No No
5826/02/2018CW2No No
5926/02/2018ST7No No
6026/02/2018ST5No No
6126/02/2018ST4No No
6226/02/2018ST3No No
6326/02/2018ST11No No
6426/02/2018ST10No No
6526/02/2018ST14No No
6626/02/2018DE6No No
6726/02/2018DE65No No
6826/02/2018DE73No No
6926/02/2018DE24No No
7026/02/2018DE72No No
7126/02/2018DE74No No
7226/02/2018LE12No No
7326/02/2018LE67No No
7426/02/2018LE6No No
7526/02/2018LE7No No
7626/02/2018LE4No YesLE4
7726/02/2018LE9No No
7826/02/2018LE3No No
7926/02/2018LE19No No
8026/02/2018LE10No No
8126/02/2018CV11No No
8226/02/2018CV12No No
8326/02/2018CV7YesCV7No
8426/02/2018CV2No YesCV2
8526/02/2018CV6No No
8626/02/2018B46No No
8726/02/2018B37No No
8826/02/2018B40No No
8926/02/2018B92No No
9026/02/2018B91No No
9126/02/2018B90No No
9226/02/2018B94No No
9326/02/2018B48No No
9426/02/2018B45No No
9526/02/2018B60No No
9626/02/2018B61No No
9726/02/2018WR9No No
9826/02/2018WR3No No
9926/02/2018WR4No No
10026/02/2018WR5No No
10126/02/2018WR7No No
10226/02/2018WR10No No
10326/02/2018WR11No YesWR11
10426/02/2018WR8No No
10526/02/2018GL20No No
10626/02/2018GL19No No
10726/02/2018HR8No No
10826/02/2018GL18No No
10926/02/2018HR9No No
11026/02/2018NP25No No
11126/02/2018NP15No No
11226/02/2018NP7No No
11326/02/2018NP4No No
11426/02/2018NP44No No

What I am trying to achieve is to just pull out the delivery/collection postcode. However, I only want to do this when a delivery is then followed onto a collection.

So, from the data above I only want to see this output

Delivery Postcode     Collect Postcode

ST13                          CH3

LE4                            CV7

In summary I am trying to calculate the mileage between two postcodes when a vehicle goes from a delivery destination to a collection destination.

I hope this makes sense?!

Thanks

1 Solution

Accepted Solutions
sumanta12
Contributor II

Re: Delivery/Collection fields

Hi Jamel,

You can try like this

TMP_DELIVERY:

LOAD

     *,

     IF(collection_postcode=postcode,PREVIOUS(postcode)) AS DEL_POST;

LOAD

[Row No.],

    Date,

    postcode,

    collection,

    collection_postcode,

    delivery,

    delivery_postcode

FROM

[https://community.qlik.com/thread/292820]

(html, codepage is 1252, embedded labels, table is @1) WHERE delivery='Yes' OR collection='Yes';

NOCONCATENATE

DELIVERY:

LOAD

*

RESIDENT TMP_DELIVERY WHERE LEN(DEL_POST)>0 AND DEL_POST<>collection_postcode ORDER BY [Row No.];

DROP TABLE TMP_DELIVERY;


Capture.PNG

7 Replies
jamelmessaoud
Contributor

Re: Delivery/Collection fields

Sorry I missed off a delivery to a collection point from the data above.  So output I want is

Delivery Postcode     Collect Postcode

ST13                          CH3

WN8                          WA10

WA7                           CH3

LE4                            CV7

JustinDallas
Valued Contributor

Re: Delivery/Collection fields

I'm confused about how your are going from your source data to your desired data set.  Why don't you limit your example data set to no  more than 10-15 rows.  It's hard to parse the question having to make such mighty scrolls between your data and your question text.

jamelmessaoud
Contributor

Re: Delivery/Collection fields

The data set is basically a vehicle's journey throughout the day so the data will get longer (depending on how far the vehicle has travelled)...

sumanta12
Contributor II

Re: Delivery/Collection fields

Hi Jamel,

You can try like this

TMP_DELIVERY:

LOAD

     *,

     IF(collection_postcode=postcode,PREVIOUS(postcode)) AS DEL_POST;

LOAD

[Row No.],

    Date,

    postcode,

    collection,

    collection_postcode,

    delivery,

    delivery_postcode

FROM

[https://community.qlik.com/thread/292820]

(html, codepage is 1252, embedded labels, table is @1) WHERE delivery='Yes' OR collection='Yes';

NOCONCATENATE

DELIVERY:

LOAD

*

RESIDENT TMP_DELIVERY WHERE LEN(DEL_POST)>0 AND DEL_POST<>collection_postcode ORDER BY [Row No.];

DROP TABLE TMP_DELIVERY;


Capture.PNG

pritamb89
Contributor

Re: Delivery/Collection fields

Check this one also

Tab1:

LOAD [Row No.],

     Date,

     postcode,

     collection,

     collection_postcode,

     delivery,

     delivery_postcode

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet1)

Where collection='Yes' or delivery='Yes';


Tab2:

NoConcatenate

Load

Previous(delivery_postcode) as delivery_postcode,

collection_postcode

Resident Tab1 where Not Exists(delivery_postcode) and Not IsNull(Previous(delivery_postcode)) order by [Row No.];

Drop Table Tab1;

jamelmessaoud
Contributor

Re: Delivery/Collection fields

Thanks for your advice guys.  It is much appreciated.

I will certainly give these a try and let you know how i get on!

jamelmessaoud
Contributor

Re: Delivery/Collection fields

Hi Sumanta,

Your solution worked perfectly.  Thank you so much for your assistance