5 Replies Latest reply: Dec 2, 2014 10:15 AM by Josh Campbell RSS

    Qlik Sense & Syntax for Mapping

    Josh Campbell

      I have a Qlik Sense file in which I'm trying to use a script from a QlikView document wherein I perform some field name changes. Here's the script:

      FieldMap:

      MAPPING

      LOAD

           oldnames,

           newnames

      FROM

      [..\Lookup Tables\FieldMappings.xlsx]

      (ooxml, embedded labels, table is FieldMappings);

      RENAME Fields using FieldMap;

       

      This will not work with Qlik Sense. I tried making a change using the data connection syntax, e.g.

      FieldMap:

      MAPPING

      LOAD

           oldnames,

           newnames

      FROM

      [lib://Scripts (network_servername)/Lookup Tables/FieldMappings.xlsx]

      (ooxml, embedded labels, table is FieldMappings);

      RENAME Fields using FieldMap;

       

      That did not work either. Any suggestions? The is the last bit to get my script fully functioning in Qlik Sense.

        • Re: Qlik Sense & Syntax for Mapping
          Jonathan Poole

          This did work for me using the lib// syntax .

           

          Book2 below is my data file. It has a field called 'A'. 

          My mapping load is book1 which has a row for A to rename as B

           

          Capture.PNG.png

           

          Here is the script using your code for the mapping load

           

          Data:

          LOAD

              A,

              Value

          FROM [lib://temp/Book2.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          FieldMap:

          mapping

          LOAD

              oldnames,

              newnames

          FROM [lib://temp/Book1.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          RENAME Fields using FieldMap;

           

           

          ...you can see that the resulting data model did have field 'A' renamed as 'B'

          Capture.PNG.png

            • Re: Re: Qlik Sense & Syntax for Mapping
              Josh Campbell

              I added my response to the wrong post earlier. Sorry about that...

               

              Jonathan,


              Thanks for your help. That's the syntax / format I'm using. I also tried adding it via the UI by selecting it via the wizard in the 'Data load editor' but that results in an error. Please see the attached screen captures.

               

              The debugger doesn't include a very details error either (also attached). I performed a full reload but it didn't reveal much more in the error message.

               

              Thanks,

              Josh

                • Re: Qlik Sense & Syntax for Mapping
                  Jonathan Poole

                  I retriedtrying to make every aspect of the mapping load (data connection, file name, sheet name ) etc.. but it still worked for me.

                   

                  I suppose it could be your mapping load spreadsheet. Do you want to upload it ?

                   

                  Also, i am using the updated version 1.0.1 released this month in case you are not.

                   

                  The only other idea is to try your mapping load in a brand new app with a simple example like mine below and work back from there.

                   

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

                   

                  Data:

                  LOAD

                      A,

                      Value

                  FROM [lib://temp/Book2.xlsx]

                  (ooxml, embedded labels, table is Sheet1);

                   

                  FieldMap:

                  mapping

                  LOAD

                      oldnames,

                      newnames

                  FROM [lib://Scripts (network_servername)/Lookup Tables\FieldMappings.xlsx]

                  (ooxml, embedded labels, table is FieldMappings);

                   

                  RENAME Fields using FieldMap;

                    • Re: Re: Qlik Sense & Syntax for Mapping
                      Josh Campbell

                      Jonathan,

                       

                      Could be. Attached you'll find the Excel spreadsheet I'm using. Let me know if you notice anything wrong with it.

                       

                      Thanks,

                      Josh

                        • Re: Re: Qlik Sense & Syntax for Mapping
                          Josh Campbell

                          I was finally able to get this to work. I had to do three things:

                          1. Change the file to an XLS not XLSX.

                          2. Invert one of the forward slashes in the directory syntax e.g. change "FROM [lib://Scripts (network_servername)/Lookup Tables/FieldMappings.xlsx]" to "FROM [lib://Scripts (network_servername)/Lookup Tables\FieldMappings.xlsx]".

                          3. Change "(ooxml, embedded labels, table is FieldMappings);" to "(biff, embedded labels, table is FieldMappings$);"

                           

                          That did the trick. So the final version looks like this:

                          FieldMap:

                          MAPPING

                          LOAD

                               oldnames,

                               newnames

                          FROM [lib://Scripts (network_servername)/Lookup Tables\FieldMappings.xlsx]

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

                          RENAME Fields using FieldMap;