1 Reply Latest reply: Jan 5, 2017 7:46 AM by Jacob Baruch RSS

    How to avoid an error if one field isn't found??

    adonay carmona

      Hi, hope you can help me, I'm new to QlikSense and same goes for programming.

       

      My process is as follow: Either I download at least 6 excel files from some database source or those files are sent to me, so I'm using the data load editor from QlikSense and I load some fields from each excel file, the problem is that each file contains a minimum of 60 headings with up to 800,000 records each (so 800,000 x 60 x 6 = 288,000,000 a very big number), so I analyzed the data and created a Script to just Load the specific fields to be used (instead of loading 60 headings, just load 30 or 28, that multiply per 800,000 multiply per 6 files, is a smaller number and easy to work with)  the problem is that not all fields are in the excel sheet, so every time I click "Load data"  and QlikSense never finds a field, an error occurs and prevents me from continuing , so I have to manually remove the field name from the script or include an empty field with the specified name in the Excel sheet. Example:


      [Table 1]

          LOAD

              [Field 1],

              [Field 2],

              .

              .

              .

              [Field i] as [Field J],

              .

              .

              .

              [Field 100]

          FROM [lib://My_Connection_name/File_Name_1.xlsx]

              (ooxml, embedded labels);

       

      [Table 2]

          LOAD

              [Field 1],

              [Field 2],

              .

              .

              .

              [Field q] as [Field R],

              .

              .

              .

              [Field 100]

          FROM [lib://My_Connection_name/File_Name_2.xlsx]

              (ooxml, embedded labels);


           In this example, let's say the "File_Name_1" doesn't have "Field 2", QlikSense at this point will display an error and I will have to manually correct it like I explain before. Is there some way I can tell QlikSense that if the field isn't in the file (i.e."Field 2" from "File_Name_1") continue until loading the rest of the fields from the remaining files?


           Later I tried:

          

      [Table1]

          LOAD * Inline [ "Field 1","Field 2",...,"Field 30" ]

          alias "Field i" as "Field J"

          concatenate (Table1) load *

          FROM [lib://My_Connection_name/File_Name.xlsx] (ooxml, embedded labels);

       

      a suggestion made by The specified item was not found. and I thought it had worked, but keeps loading the rest of the fields (all the 100 fields instead of the 30 or less that I analyzed were used ), so I've closed the app too many times because the info simply doesn't finish loading. I tried using the instruction "JOIN" but maybe I don't finish comprehending it, because either the result is: headings with no information (empty values) or all the information from the "File_Name_.xlsx". (all the headings with all their respective values).


           Help...please!!!

        • Re: How to avoid an error if one field isn't found??
          Jacob Baruch

          Hi,

           

          A bit tricky but doing the job.

          the process is to get all columns name, and then if they exist we load with there name, other we change the name to a constant, you need to that for each table.

           

          the script that I thought about:

           

          //Getting exist columns

          Headers:

          first 1 LOAD

            *

          FROM [lib://ConnectionName (hm_bi1)/TST.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          //get each field column number

          Let vField1_existence = FieldNumber('Field1','Headers');

          Let vField2_existence = FieldNumber('Field2','Headers');

          Let vField3_existence = FieldNumber('Field3','Headers');

           

          //Set field name

          let vField1_Name = if($(vField1_existence)>0,'Field1','0');

          let vField2_Name = if($(vField2_existence)>0,'Field2','0');

          let vField3_Name = if($(vField3_existence)>0,'Field3','0');

           

          drop table Headers;

           

          //load data from excel

          Data:

          Load

            $(vField1_Name) AS Field1,

              $(vField3_Name) AS Field3,

              $(vField2_Name) AS Field2

          FROM [lib://ConnectionName (hm_bi1)/TST.xlsx]

          (ooxml, embedded labels, table is Sheet1);