Using Table as Dictionary

[ABBB]

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.

[JuanGerardo · Accepted solution]

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