3 Replies Latest reply: Jun 28, 2016 9:47 AM by Sunny Talwar RSS

    Script syntax

    Chris Ellingworth

      Hi, I've tried looking at videos and help files but I just can't seem to find the right answer.

       

      I have not attempted this before so I need all of this syntax to be checked for me but in particular I think the only thing that won't work is that I don't know how to make the path correct at the end of this script. I don't know how to enter the sheet name especially I think.

       

      If my Excel file is "source.xlsx" and the relevant sheet within that workbook is called "ESA NAMES" then I am trying to use the following script to convert these codes (such as "4BAY") to names (such as "Bayside"). If someone could please help me so that I can copy and paste your amended script directly into Qlik that would be very much appreciated. Thanks.

       

      mapCode:

      mapping Load * Inline [

      Code , Description

       

      4BAY, Bayside

      4CAL, Calder


      4GOV, Goulburn


      4KIE, Kiewa

       

      4MAO, Maroondah

      4OVE, Ovens


      4PLE, Plenty

       

      ];

       

      excel:

       

      load *, applymap('mapCode', Code, '#NV') as Description From C:\Users\cellingworth\CHRIS\QLIK\DES TEST\Presentation\source.xlsx\ESA NAMES

       

       

       

        • Re: Script syntax
          Sunny Talwar

          May be like this:

           

          mapCode:

          Mapping

          LOAD * Inline [

          Code , Description

          4BAY, Bayside

          4CAL, Calder

          4GOV, Goulburn

          4KIE, Kiewa

          4MAO, Maroondah

          4OVE, Ovens

          4PLE, Plenty

          ];

           

          Excel:

          LOAD *,

                    ApplyMap('mapCode', Code, '#NV') as Description

          FROM

          [C:\Users\cellingworth\CHRIS\QLIK\DES TEST\Presentation\source.xlsx]

          (ooxml, embedded labels, table is [ESA NAME]);

           

          If you don't have embedded lables, you might need to make it no labels. Best way would be to use QlikView to create this for you using the File Wizard

           

          Capture.PNG