Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Death4Free
New Contributor III

Lookup from different data sources

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)

Code1Code2Code3
111
222444
333555666

Table B (more than 1 million rows)

Code
111
666
111

How i can get result like this:

Table B

CodeFirst_code_table_a
111111
666333
111111

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

1 Solution

Accepted Solutions
vamsee
Valued Contributor

Re: Lookup from different data sources

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;

View solution in original post

5 Replies

Re: Lookup from different data sources

try to explain the logic behind your example

vamsee
Valued Contributor

Re: Lookup from different data sources

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;

View solution in original post

Highlighted
Death4Free
New Contributor III

Re: Lookup from different data sources

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
New Contributor III

Re: Lookup from different data sources

Thank you. I will try.

Death4Free
New Contributor III

Re: Lookup from different data sources

Thank you. It help. Loading become really fast