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);

[aroyi]

Sorry I have another dumb question- how to export the new mapped table from Qlik to Excel?

[Quy_Nguyen]

Just create a Table object and add all fields as dimension. Then just right-click and select export data.

[aroyi]

Thanks Quy. That's a rather manual process. I wonder if there's any way of scripting the code to export the data, otherwise each time after mapping the data, I need to manually create tables to export the data...

[Quy_Nguyen]

Try the Store command in script. Store your data to txt or csv

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/...

[marcus_sommer]

It seems that you load this table with a wildcard - load *, - and then your applymap() will add your matches as new fields. If you want to replace the ID's with the text then you need to specify all wanted fields (and skipping the ID's).

In general it's not recommended to use the wildcard-load else to specify each field explicitly because if your source changed maybe with a new field it might lead to various problems, for example unwanted or wrongly working joins in afterwards loadings or in the datamodel.

Nevertheless sometimes it's easier to use a wildcard-load and to remove the fields afterwards, for example with:

drop fields date_of_birth, client_gender, ...; // optionally specify the table: from clients;

- Marcus

[aroyi]

Hi,

I wonder if mapping load and ApplyMap can work for loading multiple fields? As it seems simpler than the lookupvalue & returnvalue codes, plus it should just load the fields with text without the fields with codes. If I just add names for each map and give a sequence number for each field as below, would it work?

Client_DoB:

Mapping LOAD est_date_of_birth & '|' & 1, est_date_of_birth_txt

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

Client_Gender:

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

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

Clients:

Load *,

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

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

FROM [lib://unmapped/Client.xlsx]

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

[aroyi]

I rewrote the codes using Marcus's codes(thanks Marcus!), it matched the codes with words for one table but for another table(it has 21 fields to map), I keep getting the error message when trying to load data:

The following error occurred:

Field names must be unique within table

---

The error occurred here:

Episode:

LOAD

organisation_path,

episode_key,

client_key,

episode_end_date,

client_consent,

episode_completion_status,

referral_date,

principal_focus,

mental_health_treatment_plan,

homelessness,

client_postcode,

labour_force_status,

employment_participation,

income_source,

health_care_card,

ndis_participant,

marital_status,

suicide_referral_flag,

principal_diagnosis,

additional_diagnosis,

medication_antipsychotics,

medication_anxiolytics,

medication_hypnotics,

medication_antidepressants,

medication_psychostimulants,

referrer_profession,

referrer_organisation_type,

ApplyMap('Episode_Map',client_consent& '|' & 1) AS client_consent_txt,

ApplyMap('Episode_Map',episode_completion_status& '|' & 2) AS episode_completion_status_txt,

ApplyMap('Episode_Map',principal_focus& '|' & 3) AS principal_focus_txt,

ApplyMap('Episode_Map',mental_health_treatment_plan& '|' & 4) AS mental_health_treatment_plan_txt,

ApplyMap('Episode_Map',homelessness& '|' & 5) AS homelessness_txt,

ApplyMap('Episode_Map',labour_force_status& '|' & 6) AS labour_force_status_txt,

ApplyMap('Episode_Map',employment_participation& '|' & 7) AS employment_participation_txt,

ApplyMap('Episode_Map',income_source& '|' & 😎 AS income_source_txt,

ApplyMap('Episode_Map',health_care_card& '|' & 9) AS health_care_card_txt,

ApplyMap('Episode_Map',ndis_participant& '|' & 10) AS medication_anxiolytics_txt,

ApplyMap('Episode_Map',marital_status& '|' & 11) AS marital_status_txt,

ApplyMap('Episode_Map',suicide_referral_flag & '|' & 12) AS suicide_referral_flag_txt,

ApplyMap('Episode_Map',principal_diagnosis& '|' & 13) AS mprincipal_diagnosis_txt,

ApplyMap('Episode_Map',additional_diagnosis& '|' & 14) AS additional_diagnosis_txt,

ApplyMap('Episode_Map',medication_antipsychotics& '|' & 15) AS medication_antipsychotics_txt,

ApplyMap('Episode_Map',medication_anxiolytics& '|' & 16) AS medication_anxiolytics_txt,

ApplyMap('Episode_Map',medication_hypnotics& '|' & 17) AS medication_hypnotics_txt,

ApplyMap('Episode_Map',medication_antidepressants& '|' & 18) AS medication_antidepressants_txt,

ApplyMap('Episode_Map',medication_psychostimulants& '|' & 19) AS medication_psychostimulants_txt,

ApplyMap('Episode_Map',referrer_profession& '|' & 20) AS referrer_profession_txt,

ApplyMap('Episode_Map',referrer_organisation_type& '|' & 21) AS referrer_organisation_type_txt

FROM [lib://unmapped/SWS MDS - Epsiode - 201707-201806.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Episodes)

---

Can someone tell me why it didn't work? The field names are all unique, there's no duplicate field names.Thanks a lot!

[marcus_sommer]

If you do:

... load * ...

you will always load all existing fields from the source-table - completely independent if you apply any transformations to create new fields (a replacing of them is not possible). This means you should really avoid loading with * and specify your wanted fields directly.

Also you could add multiple fields with a single mapping if you concat these fields and used an additionally subfield-statement around the applymap() to split them again.

Of course this could be also done with multiple mapping-tables. Which way might be better - it depends. I personally would rather use lesser mapping-tables in both scenarios (concatenating of multiple mappings and also by returning more than a single-field) given that I would use mapping at all.

Depending on the data within your tables and the intended data-model a join of the tables and/or an associating of them within the datamodel might be more suitable.

- Marcus

[Quy_Nguyen]

Check it

[aroyi]

Thanks Quy for picking up the error in my code! I got lost in the codes....