Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
JJaky
Contributor II
Contributor II

Joining tables

Hi all, 

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

JJaky_0-1618315377299.png

 

Labels (1)
1 Solution

Accepted Solutions
jwjackso
Specialist II
Specialist II

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;

View solution in original post

3 Replies
jwjackso
Specialist II
Specialist II

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;

View solution in original post

JJaky
Contributor II
Contributor II
Author

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
Contributor III
Contributor III

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:

shayd_0-1618845355508.png

Give it another try maybe.