Joining tables

[JJaky]

Hi all, 

Any advice how can I combine two tables to get the result table below? Thanks in advance!

[jwjackso · Accepted solution]

I would use the ApplyMap  function.

mapTable2:

mapping

load City

         ,Price

From Table2:

Table1:

load Departure

         ,Stop1

         ,ApplyMap('mapTable2',Stop1,'') as Price1

        ,Stop2

        ,ApplyMap('mapTable2',Stop2,'') as Price2

       ,Stop3

      ,ApplyMap('mapTable2',Stop3,'') as Price3

      ,[Destination City]

From Table1;

[JJaky]

Hi @jwjackso , I tried mapping load, but it doesnt show the correct values. Another thing is that I was trying also to join geographical coordinates, and I got empty fields after joining. 

[shayd]

Hi JJaky,
I tried Jwjackso solution and it works perfectly,
My script with Jwjackso solution:

Table1:

LOAD * Inline [
Departure, Stop1, Stop2, Stop3, Destination City
City A, C, B, '', City B
City A, C, '', '', City C
City B, A, D, '', City D
City B, A, C, K, K
];
Table2:

LOAD * Inline [
City, Price
A, 20
B, 15
C, 5
D, 10
K, 7
];

mapTable2:

mapping

load
City,
Price
Resident Table2;

Final:

load
Departure,
Stop1,
ApplyMap('mapTable2',Stop1,'') as Price1,
Stop2,
ApplyMap('mapTable2',Stop2,'') as Price2,
Stop3,
ApplyMap('mapTable2',Stop3,'') as Price3,
[Destination City]

Resident Table1;
Drop Tables Table1,Table2;

Result:

Give it another try maybe.