Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Highlighted

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

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