How to build a delivery path

[leimgruber]

Hello

I have orders which a send over different locations. Now I would like to see the full path in one line but I face in problems to achieve it.

Example:

LOAD * INLINE [
Order, SendingLocation, ReceivingLocation
1, A, B
1, C, D
1, E, F
1, F, Z
2, B, G
2, G, M
2, M, A
];

What I would need at the end is a table like this

Order, DeliveryPath

1, A-B-C-D-E-F-Z

2, B-G-M-A

Any help is welcome.

Thanks

[Gysbert_Wassenaar · Accepted solution]

Temp:

CrossTable(Direction, Node)

LOAD * INLINE [
Order, SendingLocation, ReceivingLocation
1, A, B
1, C, D
1, E, F
1, F, Z
2, B, G
2, G, M
2, M, A
];

Result:

LOAD Order, Concat(distinct Node, '-') as DeliveryPath

Resident Temp

Group By Order;

Drop Table Temp;

[leimgruber]

Thanks Gysbert it works perfect.