8 Replies Latest reply: Jan 6, 2017 3:21 AM by Morne van Zyl RSS

    How to load optional fields (DataLoadEditor)

    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 martinpohl 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".


           Help...please!!!

        • Re: How to load optional fields (DataLoadEditor)
          Martin Pohl

          I would create a master table

          Table:

          LOAD * INLINE [

              Field1

          ];

          and than concatenate all tables to the master table with a Wildcard.

           

          concatenate (Table) load *

          FROM [lib://REGIONES/SR.xlsx] (ooxml, embedded labels);


          Here you can see Wildcards for example to load all Excels *.xlsx

          or some S*.xlsx


          Regards

            • Re: How to load optional fields (DataLoadEditor)
              adonay carmona

              I'm sorry martinpohl I thought it had helped me, but not quite. Instead of just loading all the fields named after the "Inline-statement" all the fields were loaded, the app crashes and prevents me from continuing.

                • Re: How to load optional fields (DataLoadEditor)
                  Rob Wunderlich

                  I believe Martin's INLINE was just a dummy table to give you something to Concatenate to. You don't need to list the actual fields. Another way to write this would be:

                   

                  Table:

                  LOAD 1 as DummyField

                  AutoGenerate 0;

                   

                  concatenate (Table) load *

                  FROM [lib://REGIONES/SR.xlsx] (ooxml, embedded labels);


                  DROP FIELD DummyField;


                  -Rob

                  http://masterssummit.com

                  http://qlikviewcookbook.com

                    • Re: How to load optional fields (DataLoadEditor)
                      adonay carmona

                      Thank you very much for your reply rwunderlich, it did help me understand better the instruction "INLINE", however, I did as Martin Pohl suggested, due my process is...load at least 6 excel files, each containing a minimum of 60 headings with up to 800,000 records each, and by manually creating the table with the fields I need per table through the instruction "Inline" to later concatenate it with the file located on the path I'm specifying, the application doesn't collapse and I just bring those fields I need.

                       

                           On the other hand, I'm not sure if you might assist me with something, in the path I'm specifying, there are at least 6 files, the problem I'm facing is that some of those files don't tend to have the same file extension, for example,  some of them are ".xlsx", ".xls", ".xlsm".  What I'm doing is I'm manually changing each file extension to the one I specified in my script (".xlsx"), this is very time-consuming, so do you think there might be some way to specify a file path without getting an error if the file extension is different from the one specified in the script? i.e. in my example the file name is "SR" but with extensions variations such as...SR.xlsx, SR.xls or SR.xlsm, but there are also variations among the files names (contained in the folder specified in the path), some will be named SR, others BI, others CMR, etc, wth their respective extensions variations.

                       

                           By the way....thank  you for the links.

                        • Re: How to load optional fields (DataLoadEditor)
                          Rob Wunderlich

                          xlsx and xls are two different formats that require different parameters for the filespec. "ooxml" for xlsx/xlsm, and "bif" for xls. The best solution I can think of is to process the files in a filelist loop and then use conditional code or a varable based on the extension. Something like:

                           

                          FOR EACH vFile in Filelist(lib://REGIONES/SR.*)

                          LET vFilespec = lower(subfield('$(vFile)', '.', -1)) = 'xls', 'biff', 'ooxml');

                          CONCATENATE (Table1) LOAD *

                                          FROM [lib://REGIONES/SR.xlsx] ($(vFilespec ), embedded labels);

                          NEXT vFile

                           

                          I haven't tested this, if you have problems please post your script.

                           

                          -Rob

                      • Re: How to load optional fields (DataLoadEditor)
                        Martin Pohl

                        is it always to rename Field5 to Field100?

                        So use my script, after load insert

                        Rename field Field5 to Field100;

                         

                        Regards

                         

                        Bytheway:

                        If the field Name as no space or Special caracters like *. / or - you don't Need to use "" or []

                          • Re: How to load optional fields (DataLoadEditor)
                            adonay carmona

                            Thank you for your assistance martinpohl, I did try to rename the fields,  however, for those fields that have the same content but different heading, I used an homologated name that comes after the "...as [new_field_name]". When using the instruction "Rename" an error occurs, due QlikSense is not letting me use the same "new_field_name" for more than one field. I did find a workaround,

                             

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

                             

                            but now I'm struggling to have QlikSense include those fields into a SyntheticKey... unfortunately my problem continues...sorry to bother you.

                      • Re: How to load optional fields (DataLoadEditor)
                        Morne van Zyl

                        Not sure if you've solved this already but I've had a similar problem before. I'm not as experienced as these other two chaps but I'll post my solution anyway. Might be useful.

                         

                        So, what I did was to load the first line of data from the Excel doc into a temp table.

                         

                        "CheckFieldExists":

                        noconcatenate First 1 LOAD *

                        FROM

                        [C:\Temp\Book1.xlsx]

                        (ooxml, embedded labels, table is Sheet1);

                         

                        Then check if the column exists and set it as a variable (You would have to do this for all 26 columns)

                         

                        if FieldNumber('ColumnName','CheckFieldExists') > 0 then

                            LET ColumnName = 'ColumnName';

                        else

                            SET ColumnName = "'Column Missing'";

                        end if;

                         

                        Drop the temp table

                        drop table CheckFieldExists;

                         

                        Then do a full load from the Excel document using the variables. If the Column exists, it will use it and if it doesn't exist it will use the default value you set in the 'else' part of the column check.

                         

                        'Final Load':

                        LOAD

                        $(ColumnName) as [ColumnName]

                        FROM

                        [C:\Temp\Book1.xlsx]

                        (ooxml, embedded labels, table is Sheet1);

                         

                        Now, I'm not sure how performant this script is with using variables but it worked well for me.