Qlik Community

Ask a Question

Connectivity & Data Prep

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

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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.