10 Replies Latest reply: May 31, 2012 6:28 AM by Deepak Vadithala RSS

    Load variable amount of records in script

    Stijn Brughmans

      I have a set of Excel-files, all with the same column headers but with a vairable amount of rows which contain data. Below the rows with the data I want to retreive, some other calculations are performed.

       

      An example is given in the uploaded file:

      • In this file two sets of data (in reality representing two files) are in sheets "SourceFile1" and "SourceFile2".
      • Only the data in yellow are to be imported.
      • The headers of the columns (in blue) are used as header in Qlikview as well and are identical for all source-files.
      • The data in red (in the same columns as the yellow cells) need to be ignored on importation.
      • The basic result in Qlikview is shown in sheet "Output in Qlikview".

       

      If I load the whole sheet, I become rubbish in the tables because I only need the base data (product names and amounts = yellow cells) but all data (also the calculations) are imported and shown in my table.

       

      Extra information on the source-files: The structures is always the same.

      • Row1: Column header
      • Row2: White line
      • Row3...X: Products
      • Row X+1: White line
      • Row X+3: Start other calculations (to be ignored in script).

       

      The only variable is here "X" (=amount of products), but is unknown at time of executing the script.

       

      Can someone help me to create a LOAD-statement that can perform the above ?

       

      Thanks in advance !

       

      Message was edited by: brugst13 upon request from Deepak Vadithala

        • Re: Load variable amount of records in script
          Deepak Vadithala

          Hi,

           

          I'm not sure if I have understood your question completely. Looking at the excel file do you want to load only the range A3:B11 in your excel file, Is this right? If not please can you paste the expected output in another excel file? And I'm sure one of us from community would help you.

           

          Thanks,

          DV

          www.QlikShare.com

            • Load variable amount of records in script
              Stijn Brughmans

              Hi,

               

              I tried to clarify the problem a little bit more in the text above and in the attachment.

               

              Thanks in advance for the quick response !

                • Re: Load variable amount of records in script
                  Deepak Vadithala

                  Hi,

                   

                  I have made some progress with the following assumptions.Please let me know if these assumptions are invalid!

                   

                  1. I'm assuming that all Excel files are located under same folder.
                  2. All the Excel file contains same WorkSheet name. Incase, if the names are different then we need to tweak the below code.
                  3. 'Total sold' value under [Product Name] field and it is standard word and spelling across all the Excel files. So I have used 'Total sold' in each Excel sheet/workbook and loaded all the rows before 'Total sold' occurrence.

                   

                  PS : This is not finished and it is WIP script. I'll update you tomorrow with complete script. Meanwhile, you can look at the script and let me know if this idea works.

                  ______________________________________________________________________

                   

                  For each vFileName in FileList('.\*.xls')

                  LET x = $(x) + 1;

                  LET vPK = 0;

                   

                  TestTable:

                  LOAD

                  'SourceFile' & $(x) AS SourceFileName,

                  [Product name],

                       [Amount sold],

                       RowNo() AS PK

                  FROM

                  $(vFileName)

                  (biff, embedded labels, table is SourceFile1$);

                   

                  MappingLoad:

                  Mapping LOAD

                  [Product name],

                  PK

                  Resident TestTable;

                   

                  LET vPK = ApplyMap('MappingLoad', 'Total sold');

                   

                  LOAD

                  SourceFileName,

                  [Product name] AS ProductName,

                  [Amount sold] AS AmountSold

                  Resident TestTable

                  where PK < $(vPK);

                   

                  Drop Table TestTable;

                  NEXT

                  ______________________________________________________________________

                   

                  Cheers,

                  DV

                  www.QlikShare.com

                    • Load variable amount of records in script
                      Stijn Brughmans

                      The assumptions are correct.

                       

                      - All files are in the same folder and do have the same sheetname.

                      - The "Total Sold Value" is present in each file (and same spelling).

                       

                      I understand the suggested method. I think it could work, I'll give it a try as well.

                       

                      Looking forward to see your method !

                        • Re: Load variable amount of records in script
                          Deepak Vadithala

                          Hi Again,

                           

                          Okay, that's great. Below is the final script and I have changed the script. Please note that I'm not using Mapping Load within the loop because the Mapping table will be removed after full execution of the script and not within in each loop iteration. So I have created a temporary table and used the Peek() function to extract the relevant row number.

                           

                          I'm also attaching the sample QVW file for your reference. I hope this helps.

                          __________________________________________________________________________

                           

                          DIRECTORY;

                           

                          LET x = 0;

                          For each vFileName in FileList('.\*.xls')

                              LET x = $(x) + 1;

                              LET vPK = 0;

                           

                                  TestTable:

                                  LOAD

                                  'SourceFile' & $(x) AS SourceFileName,

                                  [Product name],

                                  [Amount sold],

                                  RowNo() AS PK

                                  FROM

                                  $(vFileName)

                                  (biff, embedded labels, table is SourceFile1$);

                           

                                  PK_Value:

                                  LOAD

                                  PK

                                  Resident TestTable

                                  where [Product name] = 'Total sold';

                           

                           

                              LET vPK = Peek('PK', -1, PK_Value);

                           

                              DROP Table PK_Value;

                           

                                  FinalTable:

                                  LOAD

                                  SourceFileName,

                                  [Product name] AS ProductName,

                                  [Amount sold] AS AmountSold

                                  Resident TestTable

                                  where PK < $(vPK)

                                  and IsNull([Product name])=0;

                           

                              Drop Table TestTable;

                          NEXT

                          __________________________________________________________________________

                           

                          Cheers,

                          DV

                          www.QlikShare.com

                          • Re: Load variable amount of records in script
                            Deepak Vadithala

                            Have you got chance to test the above script?

                             

                            Any update? Curious to know...

                             

                            Thanks - DV

                    • Load variable amount of records in script
                      Clever Anjos

                      Is it possible to add another column into your Excel plan that indicate which lines would be loaded?

                      • Re: Load variable amount of records in script
                        Deepak Vadithala

                        Have you got this working? Please let me know if you need more help.

                         

                        Cheers - DV