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