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

[Anonymous]

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 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!!!

[Anonymous]

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