Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
ABBB
Contributor
Contributor

Using Table as Dictionary

Hello,

I'm currently loading two tables from two separate .qvd files.

Dict:
LOAD
ID,
NAME
FROM
[C:\Dict.qvd] (qvd);

Data:
LOAD
ID1,
ID2,
ID3
FROM
[C:\Data.qvd] (qvd);

Sample:

Dict:
1, New York
2, Chicago
3, Iowa
4, Washington

Data:
1, 2, 3
2, 3, 4
3, 4, 5

How can i join those two tables, so that Dict will serve as Dictionary for Data table? Dict contain references for ID1, ID2, ID3 so I would join them by ID fields. The desired outcome table would look like this:

ID1 | NAME1 | ID2 | NAME2 | ID3 | NAME3

So for above example it would look like this:

ID1, NAME1, ID2, NAME2, ID3, NAME3
1, New York, 2, Chicago, 3, Iowa
2, Chicago, 3, Iowa, 4, Washington
3, Iowa, 4, Washington, 5, n/a

 Thanks in advance.

1 Solution

Accepted Solutions
JuanGerardo
Partner - Specialist
Partner - Specialist

Hi @ABBB, I would suggest in this case to use a mapping table:

Map_Dict:
Mapping LOAD
ID,
NAME
FROM
[C:\Dict.qvd] (qvd);

Data:
LOAD
ID1,
ApplyMap('Map_Dict', ID1) AS NAME1,
ID2,
ApplyMap('Map_Dict', ID2) AS NAME2,
ID3,
ApplyMap('Map_Dict', ID3) AS NAME3,
FROM
[C:\Data.qvd] (qvd);

JG

View solution in original post

1 Reply
JuanGerardo
Partner - Specialist
Partner - Specialist

Hi @ABBB, I would suggest in this case to use a mapping table:

Map_Dict:
Mapping LOAD
ID,
NAME
FROM
[C:\Dict.qvd] (qvd);

Data:
LOAD
ID1,
ApplyMap('Map_Dict', ID1) AS NAME1,
ID2,
ApplyMap('Map_Dict', ID2) AS NAME2,
ID3,
ApplyMap('Map_Dict', ID3) AS NAME3,
FROM
[C:\Data.qvd] (qvd);

JG