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]

Thanks Marcus. I fixed the error in the code and ran again, this time I got circular reference error message:

The following error occurred:

Circular reference:

One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. Loop(s) will automatically be cut by setting one or more tables as loosely coupled. Use Loosen Table script statement to explicitly declare loosely coupled tables.: return

The error occurred here:

Search index creation completed successfully

Circular reference:

?

0 forced error(s)

40 synthetic key(s)

------------------------------------------

I don't understand why the code worked for the two previous tables(with 6  &9 fields to map), but failed with this table with 21 fields? How do I fix the circular reference issue? There are more than 50 fields(codes) I need to map from 4 separate tables. I loaded the data dictionary(codes and words) and the data from the 4 tables in data manager first, then used data load editor for mapping data. What have I done wrong?

[marcus_sommer]

Between the tables in the datamodel should be always only one single key - this means that some fields need to be renamed to break the association and/or if they key really needs to contain multiple fields they should be combined within a single key. Often a datamodel works correctly even if there are such synthetic keys but as a best practice (especially as a starter in Qlik) you should try to avoid them.

Fare more critical are circular loops which means that an association from table1 goes to table2 and from there to table3 and then one key goes back to table1 - in this case the datamodel isn't valid anymore. For more explanations take a look here: Get started with developing qlik datamodels.

- Marcus

[aroyi]

Hi,

I just let everyone know that Marina and William's suggestion of mapping each individual map worked. Also Marcus's codes also worked.Thanks for all your help!