I am dealing with complex dataset with millions of rows. This dataset is a combination of shipments from plant to plant. Movement Types 555 & 666 are outbound shipments and 100 are inbound shipments.
Here is the sample of the dataset:
| Tx_ref |
Movement_Type |
Quantity |
Delivery_Item |
Delivery_Code |
Date |
Description |
Plant |
Region |
| 1 |
555 |
100 |
A |
123 |
04/01/2022 |
DescA123 |
Plant1 |
EMEA |
| 2 |
555 |
200 |
A |
123 |
10/01/2022 |
DescA123 |
Plant1 |
EMEA |
| 3 |
100 |
300 |
A |
123 |
11/02/2022 |
DescA123 |
Plant2 |
LATAM |
| 4 |
555 |
10 |
B |
256 |
04/03/2022 |
DescB256 |
Plant1 |
EMEA |
| 5 |
555 |
10 |
B |
256 |
04/03/2022 |
DescB256 |
Plant1 |
EMEA |
| 6 |
666 |
300 |
A |
123 |
22/02/2022 |
DescA123 |
Plant2 |
LATAM |
| 7 |
100 |
20 |
B |
256 |
22/03/2022 |
DescB256 |
Plant3 |
APAC |
| 8 |
100 |
300 |
A |
123 |
12/04/2022 |
DescA123 |
Plant3 |
APAC |
| 9 |
666 |
5 |
C |
123 |
12/04/2022 |
DescC123 |
Plant1 |
EMEA |
| 10 |
555 |
5 |
D |
123 |
12/04/2022 |
DescD123 |
Plant1 |
EMEA |
| 11 |
666 |
5 |
C |
123 |
12/04/2022 |
DescC123 |
Plant1 |
EMEA |
| 12 |
100 |
5 |
C |
123 |
20/04/2022 |
DescC123 |
Plant2 |
LATAM |
For outbound transactions, the date/plant/region refers the shipping date/plant/region and for inbound transactions, the date/plant/region refers the receiving date/plant/region.
The key to matching the shipment to the receipt is by the combination of Delivery_Item and the Delivery_Code.
I need to show the data as node to node i.e. starting from shipping plant to end receiving plant. For example: 100 shipped from plant1 on 04/01, 200 shipped from plant1 on 10/01, 300 received at plant2 on 11/02, again this 300 shipped from plant2 on 22/02 and received at plant3 on 12/04. Over here, plant2 is the intermediary plant which I don't want to show. plant1 is the starting plant and plant3 is the end plant.
This is the output I required from this sample data. Just wondering how could I do:
| Delivery_Item |
Delivery_Code |
Description |
Start_Plant |
Start_Region |
End_Plant |
End_Region |
Start_Date |
End_Date |
Total Qty Shipped |
Total Qty Received |
| A |
123 |
DescA123 |
Plant1 |
EMEA |
Plant3 |
APAC |
04/01/2022 |
12/04/2022 |
300 |
300 |
| B |
256 |
DescB256 |
Plant1 |
EMEA |
Plant3 |
APAC |
04/03/2022 |
22/03/2022 |
20 |
20 |
| C |
123 |
DescC123 |
Plant1 |
EMEA |
Plant2 |
LATAM |
12/04/2022 |
20/04/2022 |
10 |
5 |
Thanks in advance....