Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
YanivZi
Contributor III
Contributor III

How to use lookup table?

I have loaded 2 csv files, one file has code and the other has code + translation. I want to add a 2 columns table to the sheet that has the codes from the 1st table with the translation from the 2nd table:

 tbl1:

load

@1 as 1code

from [tbl1.csv]

tbl2:

load 

@1 as 2code

@2 as 2translation

from [tbl2.csv]

 

Table in sheet:

code (from 1code) translation (2translation)
dog
2 cat

 

Labels (1)
1 Solution

Accepted Solutions
justISO
Specialist
Specialist

Hi, maybe just LEFT JOIN tbl2 to tbl1?:

tbl1:
load
@1 AS code
from [tbl1.csv]

//tbl2:
LEFT JOIN
load
@1 AS code,
@2 AS translation
from [tbl2.csv]

Or you can use ApplyMap():

tbl2_asMap:
mapping
load 
@1 as code
@2 as translation
from [tbl2.csv]

tbl1:
load
@1 as code,
ApplyMap('tbl2_asMap', code, '-Without tranlation-') as translation
from [tbl1.csv]

View solution in original post

4 Replies
justISO
Specialist
Specialist

Hi, maybe just LEFT JOIN tbl2 to tbl1?:

tbl1:
load
@1 AS code
from [tbl1.csv]

//tbl2:
LEFT JOIN
load
@1 AS code,
@2 AS translation
from [tbl2.csv]

Or you can use ApplyMap():

tbl2_asMap:
mapping
load 
@1 as code
@2 as translation
from [tbl2.csv]

tbl1:
load
@1 as code,
ApplyMap('tbl2_asMap', code, '-Without tranlation-') as translation
from [tbl1.csv]
YanivZi
Contributor III
Contributor III
Author

i did not understand what to put on 

'-Without tranlation-'
BrunPierre
Partner - Master II
Partner - Master II

Because it's specified, that's the value displayed when code in table tb12_asmap (Mapping table) does not match the code in table tbl1. If not specified, the value of the expression is returned as it is.

 

 

justISO
Specialist
Specialist

This part just fills 'empty space' with words '-Without translation-' if there will be no 'translation' to your 'code'. It can be removed and you will see null value instead, but sometimes it is easier to 'catch' some errors or gaps in this way.

Good example:

https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFunct...