14 Replies Latest reply: Jan 16, 2018 8:34 PM by Matthieu ABOLIVIER RSS

    For Loop to create QVD from xlsx

    Matthieu ABOLIVIER

      Hello,

       

      I have got a Script error for which I would really appreciate the support of the Qlik Community.

      Since the new year I cannot load the following script :

       

      --------------------------------------------------------------------------------------------------------------------------------------------

       

      // CASE OF THE PREVIOUS YEARS

      for Year=2012 to 2025

       

       

      for MonthNum=1 to 12

       

      Set cFILE_DATA = [lib://DATA/$(Year)/DATA - GOOD ISSUANCE - $(Year) - $(MonthNum).xlsx] ;

      set cFILE_QVD  = [lib://QVD/$(Year)/DATA - GOOD ISSUANCE - $(Year) - $(MonthNum).qvd] ;

       

      LET cFILE_DATA_SIZE = FileSize(cFILE_DATA);

      LET cFILE_QVD_SIZE = FileSize(cFILE_QVD);

       

      // CHECK IF DATA EXISTS

      if isnull(cFILE_DATA_SIZE) then

      // DO NOTHING

       

      else

      // CHECK IF QVD EXISTS

      if isnull(cFILE_QVD_SIZE) then

      //CREATE QVD FILE

       

      [DATA]:

      LOAD

       

      // Dimensions

       

      FROM $(cFILE_DATA)

      (ooxml, embedded labels, table is [DATA]);

       

      STORE [DATA] into $(cFILE_QVD) (qvd);

      DROP TABLE DATA;

       

      else

      // QVD EXISTS ALREADY => DO NOTHING

      end if

      end if

      next MonthNum;

      next Year;

       

      --------------------------------------------------------------------------------------------------------------------------------------------

      The script error tells me the "Table 'DATA' not found"

       

      It is been 2 weeks I am try to debug this issue.

      Could you please help me ?

       

      Many thanks in advance for your support.

       

      Matthieu

        • Re: For Loop to create QVD from xlsx
          thomas le gall

          Hi,

           

          Please provide the script log.

           

          Regards,

            • Re: For Loop to create QVD from xlsx
              Matthieu ABOLIVIER

              Hello Thomas,

               

              Thanks very much for your possible future support.

              The complete script is supplied above.

              The only information missing is the columns loaded.

              The script log error is this one :

               

              L'erreur suivante s'est produite:

              Table 'DATA' not found

              Emplacement de l'erreur:

              STORE [DATA] into lib://QVD/2012/DATA - GOOD ISSUANCE - 2012 - 2.qvd (qvd)

               

              The script basically goes through the first loop but stops at the second iteration.

              Last year I had no issue with the same code.

               

              Cheers,

               

              Matthieu

                • Re: For Loop to create QVD from xlsx
                  thomas le gall

                  Check your data model at second iteration then to see if the table DATA exists as expected

                   

                  With a :

                   

                  If monthnum =2 then

                  Exit script

                  Endif

                    • Re: For Loop to create QVD from xlsx
                      Matthieu ABOLIVIER

                      Hello Thomas,

                       

                      Everything seems oK.

                      All required columns are in the excel file.

                      The excel file contains a 'DATA' Tab.

                       

                      I excuted your code and I have no issue.

                      The first iteration generates a proper QVD file.

                       

                      Matthieu

                        • Re: For Loop to create QVD from xlsx
                          thomas le gall

                          I meant: stop the script at the second iteration before the "drop table" sentence.

                           

                          Then you will be able to check if the Table DATA exists in your Qlik App Data model as expected.

                           

                          The goal is to stop the code execution just before the error and understand why it does not find any DATA table

                            • Re: For Loop to create QVD from xlsx
                              Matthieu ABOLIVIER

                              Hello Thomas,

                               

                              OK, I think your idea is very good. Thank you very much.

                               

                              I can see the problem now.

                              In fact I have just created a new App and connected the first QVD generated by the code.

                               

                              It seems :

                              - the name of the table created in the QVD is wrong.

                              - the column loaded are not the correct ones (correspond to another database loaded before)

                               

                              Here is the column I want to load in QVD :

                               

                              [DATA]:

                              LOAD

                               

                                [Material],

                                [Order],

                                [Cost Center]

                                [Storage Location],

                                [Movement Type],

                                [Qty in Un. of Entry],

                                [Unit of Entry],

                                [Amount in LC],

                                [Posting Date],

                                [Time of Entry],

                                [User name],

                               

                              FROM $(cFILE_DATA)

                              (ooxml, embedded labels, table is [DATA]);

                               

                               

                              Here is the data contained in the QVD :

                               

                              [DATA - GOOD ISSUANCE - 2012 - 1]:

                              LOAD

                                [Order],

                                [WO Material Number],

                                [WO Material description],

                                [MRP controller],

                                [BOM number]

                              FROM [lib://2012/DATA - GOOD ISSUANCE - 2012 - 1.qvd]

                              (qvd);

                               

                               

                              The columns loaded are the ones of another database I loaded before.

                              The name of the table is the name of the file.

                               

                              A priori, I need to drop the table of the database loaded before or change its name.

                               

                              Can you please give me your feedback ?

                               

                              Many thanks.

                               

                              Matthieu

                                • Re: For Loop to create QVD from xlsx
                                  thomas le gall

                                  2 things to discuss :

                                   

                                  1 : if your qvd is wrong, why do you want to use it anyway ?

                                   

                                  2 : When you load columns in table with exactly the same columns as another previously loaded table, the new table is automatically concatenated to the first one.

                                   

                                  If you want to avoid that behaviour, you need to add the "noconcatenate" sentence before loading the second table, as :

                                   

                                  NoConcatenate

                                  DATA:

                                  LOAD *, etc.

                                   

                                   

                                   

                                  Regards,

                                    • Re: For Loop to create QVD from xlsx
                                      Matthieu ABOLIVIER

                                      The code now generates all QVD. The size of all QVD  varies and the columns insides are OK.

                                      I think all QVD generated are now good.

                                       

                                      The only issue is the name of the Table inside.

                                      Instead of being named :

                                      [DATA],

                                      it is automatically set to

                                      '[DATA - GOOD ISSUANCE - 2012 - 1]'

                                      '[DATA - GOOD ISSUANCE - 2012 - 2]'

                                       

                                      I guess this code :

                                       

                                      STORE [DATA] into $(cFILE_QVD) (qvd);

                                      DROP TABLE DATA;

                                       

                                      shall define the name of the table inside QVD as [DATA]

                                       

                                       

                                      Here is the full code :

                                      ------------------------------------------------------------------------------------------------------------------------------

                                       

                                      for Year=2012 to 2025

                                       

                                      for MonthNum=1 to 12

                                       

                                      Set cFILE_DATA = [lib://DATA/$(Year)/DATA - GOOD ISSUANCE - $(Year) - $(MonthNum).xlsx] ;

                                      set cFILE_QVD  = [lib://QVD/$(Year)/DATA - GOOD ISSUANCE - $(Year) - $(MonthNum).qvd] ;

                                       

                                      LET cFILE_DATA_SIZE = FileSize(cFILE_DATA);

                                      LET cFILE_QVD_SIZE = FileSize(cFILE_QVD);

                                       

                                       

                                      // CHECK IF DATA EXISTS

                                      if isnull(cFILE_DATA_SIZE) then

                                      // DO NOTHING

                                       

                                      else

                                      // CHECK IF QVD EXISTS

                                      if isnull(cFILE_QVD_SIZE) then

                                      //CREATE QVD FILE

                                       

                                      //noconcatenate

                                       

                                      [DATA]:

                                      LOAD

                                       

                                        [Material],

                                        [Order],

                                        [Cost Center],

                                        [Storage Location],

                                        [Movement Type],

                                        [Qty in Un. of Entry],

                                        [Unit of Entry],

                                         [Amount in LC],

                                        [Posting Date],

                                        [Time of Entry],

                                        [User name]

                                       

                                      FROM $(cFILE_DATA)

                                      (ooxml, embedded labels, table is [DATA]);

                                       

                                      STORE [DATA] into $(cFILE_QVD) (qvd);

                                      DROP TABLE [DATA];

                                       

                                      else

                                      // QVD EXISTS ALREADY => DO NOTHING

                                       

                                      end if

                                      end if

                                       

                                      next MonthNum;

                                      next Year;

                                       

                                      ------------------------------------------------------------------------------------------------------------------------------

                                      After the 'QVD MONTH' are generated, I concatenate the 12 Months into a QVD Year.

                                      After the 'QVD YEAR" are generated, I concatenate them into 1 QVD which is loaded into the App.

                        • Re: For Loop to create QVD from xlsx
                          Petter Skjolden

                          The point where it fails is most probably here:

                           

                          ......

                          FROM $(cFILE_DATA)

                          (ooxml, embedded labels, table is [DATA]);

                           

                          It indicates that a sheet named DATA is not to be found in the Excel workbook you're reading from. When reading from Excel the sheet is referred to as a table.

                           

                          The  way around this is to set the ErrorMode variable to 0 which tells Qlik Sense to ignore errors which will allow you to at least read all the workbooks that really has a sheet named DATA.

                           

                          Set ErrorMode=0;

                          [DATA]:

                          LOAD

                          // Dimensions

                          FROM $(cFILE_DATA)

                          (ooxml, embedded labels, table is [DATA]);

                          STORE [DATA] into $(cFILE_QVD) (qvd);

                          DROP TABLE DATA;

                          Set ErrorMode=1; // set the error handling back to normal

                          • Re: For Loop to create QVD from xlsx
                            Josefina Fasoli

                            Just to check, if you set the maximum year to 2017 the script works fine?