23 Replies Latest reply: Sep 26, 2018 7:11 PM by Nina Ni RSS

    How to load multiple fields using mapping load and applymap?

    Nina Ni

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

        • Re: How to load multiple fields using mapping load and applymap?
          Marina Dor

          Hi Nina,

           

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

           

          Regards,

          Marina

          • Re: How to load multiple fields using mapping load and applymap?
            William Fu

            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,

            • Re: How to load multiple fields using mapping load and applymap?
              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

                • Re: How to load multiple fields using mapping load and applymap?
                  Marina Dor

                  Hi Marcus,

                   

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

                    • Re: How to load multiple fields using mapping load and applymap?
                      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

                        • Re: How to load multiple fields using mapping load and applymap?
                          Nina Ni

                          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?

                            • Re: How to load multiple fields using mapping load and applymap?
                              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

                                • Re: How to load multiple fields using mapping load and applymap?
                                  Nina Ni

                                  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?

                                    • Re: How to load multiple fields using mapping load and applymap?
                                      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

                            • Re: How to load multiple fields using mapping load and applymap?
                              Nina Ni

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

                                • Re: How to load multiple fields using mapping load and applymap?
                                  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

                                    • Re: How to load multiple fields using mapping load and applymap?
                                      Nina Ni

                                      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?

                                        • Re: How to load multiple fields using mapping load and applymap?
                                          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

                                  • Re: How to load multiple fields using mapping load and applymap?
                                    Nina Ni

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

                                    • Re: How to load multiple fields using mapping load and applymap?
                                      Nina Ni

                                      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& '|' & 8) 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!