How to load multiple fields using mapping load and applymap?

[aroyi]

Hi,

I have a table(table1) with multiple fields with codes, and a data dictionary table with fields of words for the codes from table1, an example as below:

client_gender         client_gender_txt

0                                Not stated/Inadequately described

1                                Male

2                                Female

3                                Other

I want to use Mapping load and ApplyMap in Qliksense to load a new table to replace codes in table1 using the words from the dictionary. Below are the code I wrote in data load editor, it did replaced codes with words, however some of the fields came with words from wrong fields despite I wrote the correct fields.Can someone tell me what's wrong? Thanks a lot! I've googled this topic but haven't found a solution to mine.

Client_Map:

Mapping LOAD date_of_birth,date_of_birth_txt from [lib://dictionary.xls]

(biff, embedded labels, table is date_of_birth$);

Mapping LOAD client_gender,client_gender_txt from [lib://dictionary.xls]

(biff, embedded labels, table is client_gender$);

Mapping LOAD client_status,client_status_txt from [lib://dictionary.xls]

(biff, embedded labels, table is client_status$);

Mapping LOAD country_of_birth,country_of_birth_txt from [lib://dictionary.xls]

(biff, embedded labels, table is country_of_birth$);

Mapping LOAD main_lang_at_home,main_lang_at_home_txt from [lib://dictionary.xls]

(biff, embedded labels, table is main_lang_at_home$);

Mapping LOAD prof_english,prof_english_txt from [lib://dictionary.xls]

(biff, embedded labels, table is prof_english$);

Clients:

Load *,

ApplyMap('Client_Map',date_of_birth) AS est_date_of_birth_txt,

ApplyMap('Client_Map',client_gender) AS client_gender_txt,

ApplyMap('Client_Map',client_status) AS client_status_txt,

ApplyMap('Client_Map',country_of_birth) AS country_of_birth_txt,

ApplyMap('Client_Map',main_lang_at_home) AS main_lang_at_home_txt,

ApplyMap('Client_Map',prof_english) AS prof_english_txt

From [lib:// Client.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Clients);

[marinadorcassio1]

Hi Nina,

You have to name each mapping with a different name, it will work

Regards,

Marina

[william_fu · Accepted solution]

I think you need one mapping table for each ApplyMap, something like this:

Client_DoB:

Mapping LOAD date_of_birth,date_of_birth_txt from [lib://dictionary.xls]

(biff, embedded labels, table is date_of_birth$);

Client_Gender:

Mapping LOAD client_gender,client_gender_txt from [lib://dictionary.xls]

(biff, embedded labels, table is client_gender$);

Client_Status:

Mapping LOAD client_status,client_status_txt from [lib://dictionary.xls]

(biff, embedded labels, table is client_status$);

Clients:

Load *,

ApplyMap('Client_DoB',date_of_birth) AS est_date_of_birth_txt,

ApplyMap('Client_Gender',client_gender) AS client_gender_txt,

ApplyMap('Client_Status',client_status) AS client_status_txt,

[marcus_sommer]

In general needs each applymap() an own mapping-table but there are scenarios in which they could be matched in a lower number of mapping-tables or even a single-table. In your case it looked as if the mappings could be combined because it looked rather unlikely that there are overlapping values between them.

That your approach failed is caused through the fact that mapping-tables couldn't be (valid) created with loops and/or concatenation of files else it should be created with a single-load. Therefore try something like this:

Client_Map_TEMP:

LOAD date_of_birth as LookupValue, date_of_birth_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is date_of_birth$);

     concatenate(Client_Map_TEMP)

LOAD client_gender as LookupValue, client_gender_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is client_gender$);

     concatenate(Client_Map_TEMP)

LOAD client_status as LookupValue, client_status_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is client_status$);

     concatenate(Client_Map_TEMP)

LOAD country_of_birth as LookupValue, country_of_birth_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is country_of_birth$);

     concatenate(Client_Map_TEMP)

LOAD main_lang_at_home as LookupValue, main_lang_at_home_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is main_lang_at_home$);

     concatenate(Client_Map_TEMP)

LOAD prof_english as LookupValue, prof_english_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is prof_english$);

Client_Map:

mapping load * resident Client_Map_TEMP;

drop tables Client_Map_TEMP;

Clients:

Load *,

ApplyMap('Client_Map',date_of_birth) AS est_date_of_birth_txt,

ApplyMap('Client_Map',client_gender) AS client_gender_txt,

ApplyMap('Client_Map',client_status) AS client_status_txt,

ApplyMap('Client_Map',country_of_birth) AS country_of_birth_txt,

ApplyMap('Client_Map',main_lang_at_home) AS main_lang_at_home_txt,

ApplyMap('Client_Map',prof_english) AS prof_english_txt

From [lib:// Client.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Clients);

- Marcus

[marinadorcassio1]

Hi Marcus,

I didn't know this could work, but I imagine you have to be careful about duplicate map keys, right ?

[marcus_sommer]

Yes, you are right - it only worked properly if there are no overlapping values between the different mapping-sources which seems by a date, a status, a country and so on rather unlikely in regard to the usual text-values of them. But if they just contain a numerical ID and the mapping should add the text then it might not suitable and separate mapping-tables are necessary.

One way to overcome it might be to add a source to the mapping value maybe in this way:

Client_Map_TEMP:

LOAD date_of_birth & '|' & 1 as LookupValue, date_of_birth_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is date_of_birth$);

     concatenate(Client_Map_TEMP)

LOAD client_gender & '|' & 2 as LookupValue, client_gender_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is client_gender$);

     concatenate(Client_Map_TEMP)

LOAD client_status & '|' & 3 as LookupValue, client_status_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is client_status$);

     concatenate(Client_Map_TEMP)

LOAD country_of_birth & '|' & 4 as LookupValue, country_of_birth_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is country_of_birth$);

     concatenate(Client_Map_TEMP)

LOAD main_lang_at_home & '|' & 5 as LookupValue, main_lang_at_home_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is main_lang_at_home$);

     concatenate(Client_Map_TEMP)

LOAD prof_english & '|' & 6 as LookupValue, prof_english_txt as ReturnValue

from [lib://dictionary.xls] (biff, embedded labels, table is prof_english$);

Client_Map:

mapping load * resident Client_Map_TEMP;

drop tables Client_Map_TEMP;

Clients:

Load *,

ApplyMap('Client_Map',date_of_birth & '|' & 1) AS est_date_of_birth_txt,

ApplyMap('Client_Map',client_gender & '|' & 2) AS client_gender_txt,

ApplyMap('Client_Map',client_status & '|' & 3) AS client_status_txt,

ApplyMap('Client_Map',country_of_birth & '|' & 4) AS country_of_birth_txt,

ApplyMap('Client_Map',main_lang_at_home & '|' & 5) AS main_lang_at_home_txt,

ApplyMap('Client_Map',prof_english & '|' & 6) AS prof_english_txt

- Marcus

[aroyi]

Hi Marina and William,

I've tried with individual map names, but it didn't work-error message:map_id not found.

[aroyi]

Thanks Marcus.I've tried with your codes with numerical ID for each map, but got this error message(the fields names are correct):

"The following error occurred:

Cannot open file: 'lib://MDS data mapping/pmhc_dictionary.xls] (biff, embedded labels, table is client_atsi_status$);

     concatenate(Client_Map_TEMP)

LOAD country_of_birth & '|' & 4 as LookupValue, country_of_birth_txt as ReturnValue

from [lib://MDS data mapping/pmhc_dictionary.xls'

The error occurred here:

concatenate(Client_Map_TEMP)

LOAD client_atsi_status & '|' & 3 as LookupValue, client_atsi_status_txt as ReturnValue

from [lib://MDS data mapping/pmhc_dictionary.xls]] (biff, embedded labels, table is client_atsi_status$);

     concatenate(Client_Map_TEMP)

LOAD country_of_birth & '|' & 4 as LookupValue, country_of_birth_txt as ReturnValue

from [lib://MDS data mapping/pmhc_dictionary.xls] (biff, embedded labels, table is country_of_birth$)

Data has not been loaded. Please correct the error and try loading again."

What went wrong?

[marcus_sommer]

The error means that either the filepath and/or the sheet-name and/or the fileformat isn't correct - maybe a typo or xlsx instead of xls. I didn't touched it here (just copy  paste from your code) - therefore check again that everything is right.

-Marcus

[aroyi]

Thanks Marcus, I've fixed the error with file path and ran the code again, it worked. However, in the new table it created, the fields with codes are still there, along with fields with words. Why were the fields with codes not replaced by fields with words?