Link differently named columns in different tables

[#1]

Hi,

I have two different tables, A and B.

In table A I have two columns, let's say 'Code 1' and 'Code 2', which have codes in them (different codes to each other).

In table B I have two columns ('Code' and 'Translation'). 'Code' contains the codes from both 'Code 1' and 'Code 2' from table A (all jumbled up together) and 'Translation' contains an explanation of what the code means.

I would like to either link 'Code 1' to 'Code' and 'Code 2' to 'Code' in order to return 'Translation' in my dashboard, or add two columns to table A which contains the code translations for 'Code 1' and 'Code 2'.

No other fields in table A or B link together.

The perils of freely available healthcare data! Thanks in advance for any help.

Cheers

Kev

[#2]

If the arrangement of TableB's code is as follows:

TableA
Code1    Code2

123          8731

TableB

Code           Translation

1238731       Blablabla

I believe you could create another column in TableA. Insert the following after the Load sentence of your TableA: Code1&Code2 as Code

[maxgro · Accepted solution]

you can use a mapping load: first load table B, then table A using B

MapB:

mapping load

  rowno() as Code,

  'translation of ' & rowno() as Translation

autogenerate 6; 

A:

load

Code1, Code2,

ApplyMap('MapB', Code1) as Code1Translation,

ApplyMap('MapB', Code2) as Code2Translation

inline [

Code1, Code2

1, 2

3, 4

5, 6

];

[#4]

Thank you both very much for your responses.

Eduardo - the codes are not linked in that way. Table B would be:

Code     Translation

123          Bla

8731        BlaDeBla

Massimo, many thanks, spot on!