Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
This is really difficult to explain but here goes....
I have some data which is loaded like this
Row No. | Date | postcode | collection | collection_postcode | delivery | delivery_postcode |
1 | 23/02/2018 | WS11 | No | No | ||
2 | 23/02/2018 | WV10 | No | No | ||
3 | 23/02/2018 | WV11 | No | No | ||
4 | 23/02/2018 | WV12 | No | No | ||
5 | 23/02/2018 | WS2 | No | No | ||
6 | 23/02/2018 | WS5 | No | No | ||
7 | 23/02/2018 | WS10 | No | No | ||
8 | 23/02/2018 | B71 | No | No | ||
9 | 23/02/2018 | B70 | No | No | ||
10 | 23/02/2018 | B69 | No | No | ||
11 | 23/02/2018 | DY4 | No | No | ||
12 | 23/02/2018 | DY1 | No | No | ||
13 | 23/02/2018 | DY2 | No | No | ||
14 | 23/02/2018 | DY5 | No | Yes | DY5 | |
15 | 23/02/2018 | WV14 | No | No | ||
16 | 23/02/2018 | WV13 | No | No | ||
17 | 23/02/2018 | ST19 | No | No | ||
18 | 23/02/2018 | ST18 | No | No | ||
19 | 23/02/2018 | ST17 | No | No | ||
20 | 23/02/2018 | ST15 | No | No | ||
21 | 23/02/2018 | ST4 | No | No | ||
22 | 23/02/2018 | ST5 | No | No | ||
23 | 23/02/2018 | ST3 | No | No | ||
24 | 23/02/2018 | ST11 | No | No | ||
25 | 23/02/2018 | ST9 | No | No | ||
26 | 23/02/2018 | ST13 | No | Yes | ST13 | |
27 | 23/02/2018 | ST2 | No | No | ||
28 | 23/02/2018 | ST6 | No | No | ||
29 | 23/02/2018 | ST1 | No | No | ||
30 | 23/02/2018 | ST7 | No | No | ||
31 | 23/02/2018 | CW2 | No | No | ||
32 | 23/02/2018 | CW5 | No | No | ||
33 | 23/02/2018 | CW6 | No | No | ||
34 | 23/02/2018 | CH3 | Yes | CH3 | No | |
35 | 23/02/2018 | CW8 | No | No | ||
36 | 23/02/2018 | WA4 | No | No | ||
37 | 23/02/2018 | WA16 | No | No | ||
38 | 23/02/2018 | WA13 | No | No | ||
39 | 23/02/2018 | WA1 | No | No | ||
40 | 23/02/2018 | WA2 | No | No | ||
41 | 23/02/2018 | WA3 | No | No | ||
42 | 23/02/2018 | WA12 | No | No | ||
43 | 23/02/2018 | WN4 | No | No | ||
44 | 23/02/2018 | WN5 | No | No | ||
45 | 23/02/2018 | WN8 | No | Yes | WN8 | |
46 | 23/02/2018 | WA11 | No | No | ||
47 | 23/02/2018 | WA9 | No | No | ||
48 | 23/02/2018 | WA10 | Yes | WA10 | No | |
49 | 23/02/2018 | L35 | No | No | ||
50 | 23/02/2018 | WA8 | No | No | ||
51 | 23/02/2018 | WA7 | No | Yes | WA7 | |
52 | 23/02/2018 | WA6 | No | No | ||
53 | 23/02/2018 | CH2 | No | No | ||
54 | 24/02/2018 | CH3 | Yes | CH3 | No | |
55 | 26/02/2018 | CH3 | Yes | CH3 | No | |
56 | 26/02/2018 | CW6 | No | No | ||
57 | 26/02/2018 | CW5 | No | No | ||
58 | 26/02/2018 | CW2 | No | No | ||
59 | 26/02/2018 | ST7 | No | No | ||
60 | 26/02/2018 | ST5 | No | No | ||
61 | 26/02/2018 | ST4 | No | No | ||
62 | 26/02/2018 | ST3 | No | No | ||
63 | 26/02/2018 | ST11 | No | No | ||
64 | 26/02/2018 | ST10 | No | No | ||
65 | 26/02/2018 | ST14 | No | No | ||
66 | 26/02/2018 | DE6 | No | No | ||
67 | 26/02/2018 | DE65 | No | No | ||
68 | 26/02/2018 | DE73 | No | No | ||
69 | 26/02/2018 | DE24 | No | No | ||
70 | 26/02/2018 | DE72 | No | No | ||
71 | 26/02/2018 | DE74 | No | No | ||
72 | 26/02/2018 | LE12 | No | No | ||
73 | 26/02/2018 | LE67 | No | No | ||
74 | 26/02/2018 | LE6 | No | No | ||
75 | 26/02/2018 | LE7 | No | No | ||
76 | 26/02/2018 | LE4 | No | Yes | LE4 | |
77 | 26/02/2018 | LE9 | No | No | ||
78 | 26/02/2018 | LE3 | No | No | ||
79 | 26/02/2018 | LE19 | No | No | ||
80 | 26/02/2018 | LE10 | No | No | ||
81 | 26/02/2018 | CV11 | No | No | ||
82 | 26/02/2018 | CV12 | No | No | ||
83 | 26/02/2018 | CV7 | Yes | CV7 | No | |
84 | 26/02/2018 | CV2 | No | Yes | CV2 | |
85 | 26/02/2018 | CV6 | No | No | ||
86 | 26/02/2018 | B46 | No | No | ||
87 | 26/02/2018 | B37 | No | No | ||
88 | 26/02/2018 | B40 | No | No | ||
89 | 26/02/2018 | B92 | No | No | ||
90 | 26/02/2018 | B91 | No | No | ||
91 | 26/02/2018 | B90 | No | No | ||
92 | 26/02/2018 | B94 | No | No | ||
93 | 26/02/2018 | B48 | No | No | ||
94 | 26/02/2018 | B45 | No | No | ||
95 | 26/02/2018 | B60 | No | No | ||
96 | 26/02/2018 | B61 | No | No | ||
97 | 26/02/2018 | WR9 | No | No | ||
98 | 26/02/2018 | WR3 | No | No | ||
99 | 26/02/2018 | WR4 | No | No | ||
100 | 26/02/2018 | WR5 | No | No | ||
101 | 26/02/2018 | WR7 | No | No | ||
102 | 26/02/2018 | WR10 | No | No | ||
103 | 26/02/2018 | WR11 | No | Yes | WR11 | |
104 | 26/02/2018 | WR8 | No | No | ||
105 | 26/02/2018 | GL20 | No | No | ||
106 | 26/02/2018 | GL19 | No | No | ||
107 | 26/02/2018 | HR8 | No | No | ||
108 | 26/02/2018 | GL18 | No | No | ||
109 | 26/02/2018 | HR9 | No | No | ||
110 | 26/02/2018 | NP25 | No | No | ||
111 | 26/02/2018 | NP15 | No | No | ||
112 | 26/02/2018 | NP7 | No | No | ||
113 | 26/02/2018 | NP4 | No | No | ||
114 | 26/02/2018 | NP44 | No | 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
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;
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
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.
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)...
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;
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;
Thanks for your advice guys. It is much appreciated.
I will certainly give these a try and let you know how i get on!
Hi Sumanta,
Your solution worked perfectly. Thank you so much for your assistance