Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
adoncarm
New Contributor II

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

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

1 Reply
baruchjacob
New Contributor III

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

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