3 Replies Latest reply: Jun 27, 2016 3:24 AM by Marcus Sommer RSS

    Data load editor question

    Chris Ellingworth

      Hi,

      If the spreadsheet that I am loading into Qlik has a field called "ESA_CODE" with codes in the column such as:

       

      "4Bay" (which I want to be "Bayside") and "4Cal" (which I want to be "Calder") for example (there are another 5 codes like this), then how can I set this change up in the script in the Data Load Editor so that the data loads in with these names rather than the codes?

       

      I have previously set up a drill down dimension with an expression to make this change which works but looks awful in Qlik because there is a great long expression in the top left hand corner of the pivot table or at the top of my pie graph for example to show the level of the drill down currently selected.

       

      Thanks,

        • Re: Data load editor question
          Marcus Sommer

          You could use a mapping for this, like:

           

          mapCode:

          mapping Load * Inline [

          Code , Description

          4Bay, Bayside

          4Cal, Calder

          ];

           

          excel:

          load *, applymap('mapCode', Code, '#NV') as Description From YourExcel;

           

          - Marcus

            • Re: Data load editor question
              Chris Ellingworth

              Thanks Marcus but I need some further help. Does "YourExcel" mean that I need to enter the sheet name or the file name that the data is coming from? The file is actually "CASE.xlsx" and I notice "case" comes up blue as it's also a command or something. Is this a problem? No matter what I try I keep getting the following error message in the image below (you'll notice there are 2 different Excel files being loaded also if this is a concern). Is there anything else here that is not meant to be entered verbatim? I'm just smart enough to know that I don't include "- Marcus" but not much smarter at this stage

              error.png

                • Re: Data load editor question
                  Marcus Sommer

                  "YourExcel" meant the path + file + sheet from your excel file. The easiest way to create a load-statement with the right syntax for such an excel-file ist to drag and drop this file on sense and a wizard will start and led you through a few parameter. If this is finished you could open the data load editor again and adds the applymap-statement to this load.

                   

                  A more detailed explanation for a howto will you find here: Loading Excel and File Data (video).

                   

                  - Marcus