topic Re: Load variable amount of records in script in QlikView

Load variable amount of records in script

Mon, 14 May 2012 15:45:20 GMT

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

IAMDV — Mon, 14 May 2012 16:50:57 GMT

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,

www.QlikShare.com

Load variable amount of records in script

Mon, 14 May 2012 17:30:54 GMT

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 !

Load variable amount of records in script

Clever_Anjos — Mon, 14 May 2012 19:28:56 GMT

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

IAMDV — Mon, 14 May 2012 22:21:41 GMT

Hi,

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

I'm assuming that all Excel files are located under same folder.
All the Excel file contains same WorkSheet name. Incase, if the names are different then we need to tweak the below code.
'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],

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,

www.QlikShare.com

Load variable amount of records in script

Tue, 15 May 2012 05:45:26 GMT

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

IAMDV — Tue, 15 May 2012 10:50:58 GMT

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

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,

www.QlikShare.com

Re: Load variable amount of records in script

IAMDV — Wed, 16 May 2012 09:39:15 GMT

Have you got chance to test the above script?

Any update? Curious to know...

Thanks - DV

Re: Load variable amount of records in script

IAMDV — Thu, 17 May 2012 22:32:26 GMT

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

Cheers - DV

Load variable amount of records in script

Wed, 30 May 2012 16:02:20 GMT

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 !

Re: Load variable amount of records in script

IAMDV — Thu, 31 May 2012 10:28:14 GMT

I'm glad it's working. You are welcome.

Cheers,