Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load variable amount of records in script

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

1 Solution

Accepted Solutions
IAMDV
Luminary Alumni
Luminary Alumni

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

View solution in original post

10 Replies
IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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 !

Clever_Anjos
Employee
Employee

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

IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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 !

IAMDV
Luminary Alumni
Luminary Alumni

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

IAMDV
Luminary Alumni
Luminary Alumni

Have you got chance to test the above script?

Any update? Curious to know...

Thanks - DV

IAMDV
Luminary Alumni
Luminary Alumni

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

Cheers - DV

Not applicable
Author

Hello Deepak,

sorry for the late response, but I wasn't able to try your answer until now. It works as expected when converted to the right settings.

Thanks very much for your help and suggestions !