Lookup from different data sources

[Death4Free]

Hi! What is the best way to do the following:

I have:

DataSource1 - Table A;

DataSource2 - Table B;

Table A (about 100k rows and Code1 column is unique)

Code1	Code2	Code3
111
222	444
333	555	666

Table B (more than 1 million rows)

Code
111
666
111

How i can get result like this:

Table B

Code	First_code_table_a
111	111
666	333
111	111

For now I have:

LOAD Code1, Code2, Code3;

SQL SELECT Code1, Code2, Code3

FROM Table A

LOAD Code,

if (IsNull(Lookup('Code1','Code1',Code,'Table A')) = False(),

    Lookup('Code1','Code1',Code,'Table A'),

    if (IsNull(Lookup('Code1','Code2',Code,'Table A')) = False(),

    Lookup('Code1','Code2',Code,'Table A'),

    if (IsNull(Lookup('Code1','Code3',Code,'Table A')) = False(),

    Lookup('Code1','Code3',Code,'Table A'),

    'Empty'))) AS First_code_table_a;

SQL SELECT Code

FROM Table B

But it is really slow

[MarcoWedel]

try to explain the logic behind your example

[vamsee · Accepted solution]

Hope I understood the requirement correct.

Try the following and see if its faster.

(1. Mapping loads are faster than lookup table. 2. Select * and Load * is faster while extracting data. )

TableA:
LOAD Code1, Code2, Code3;
SQL SELECT Code1, Code2, Code3
FROM Table A;
Code1_Map:
Mapping Load
Code1,
Code1
Resident TableA;
Code2_Map:
Mapping Load
Code2,
Code1
Resident TableA;
Code3_Map:
Mapping Load
Code3,
Code1
Resident TableA;
DROP Table A;
TableB:
LOAD *;
SQL SELECT Code
FROM Table B;

Final_TableB:
NoConcatenate
LOAD
Code,
ALT(ApplyMap('Code1_Map', Code,NULL()),ApplyMap('Code1_Map', Code,NULL()),ApplyMap('Code1_Map', Code,NULL()), 'Empty' ) as First_code_table_a
Resident TableB;
DROP Table B;

[Death4Free]

Ok. I will try. I want to create relationship between Table A and Table B. Table A all the time changes. In Table B there could be codes from all columns from Table A. So my variant to create column in Table B with first code from Table A and then associate it.

[Death4Free]

Thank you. I will try.

[Death4Free]

Thank you. It help. Loading become really fast