topic How to use lookup table? in App Development

How to use lookup table?

YanivZi — Thu, 25 Aug 2022 05:28:41 GMT

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)
1	dog
2	cat

Re: How to use lookup table?

justISO — Thu, 25 Aug 2022 05:37:41 GMT

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]

Re: How to use lookup table?

YanivZi — Thu, 25 Aug 2022 07:41:43 GMT

i did not understand what to put on

'-Without tranlation-'

Re: How to use lookup table?

BrunPierre — Thu, 25 Aug 2022 08:12:54 GMT

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.

Re: How to use lookup table?

justISO — Thu, 25 Aug 2022 08:15:53 GMT

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/MappingFunctions/ApplyMap.htm