topic Re: Do While Loop in QlikView

Do While Loop

brunopaulo — Wed, 25 Nov 2020 16:16:04 GMT

Hello community,

Im trying use do while loop to store in a single qvd the data from multiple excel files.

My excel files has similar names ( Store - April 2015, Store - May 2015, etc)

The excel file are saved in my computer and i dont need any connection to acess them.

I saw several articles but none fully respond to my question.

Thanks in advance

Best Regards

Bruno Paulo

Re: Do While Loop

oscar_ortiz — Fri, 06 Oct 2017 15:11:54 GMT

Using wild card load you don't need to loop through your tables.

Maybe something like this:

SET vXLSPath = 'YourXLSPathGoesHere';

SET vQVDPath = 'YourQVDPathGoesHere';

StoreData:

LOAD

From

[$(vXLSPath)Store*2015.xlsx]

(ooxml, embedded labels, table is SheetName);

StoreData:

LOAD

From

[$(vXLSPath)Store*2015.xls]

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

Store StoreData into $(vQVDPath)Your.Qvd (qvd);

Good Luck

Oscar

Re: Do While Loop

brunopaulo — Fri, 06 Oct 2017 15:39:33 GMT

I know i don't need loop but its a requirment.

Re: Do While Loop

oscar_ortiz — Fri, 06 Oct 2017 15:56:51 GMT

You may try something like this:

SET vQVDPath = 'YourQVDPathGoesHere';

Sub ReadExcel (Root)

For Each File in filelist (Root&'\*.xlsx')

YourTable:

Load * FROM [$(File)] (ooxml, embedded labels, table is YourSheet)

;

Next File

End Sub

Call ReadExcel ('YourXLSPath')

Store YourTable into $(vQVDPath)Your.Qvd (qvd);

Good luck

Oscar

Re: Do While Loop

maxgro — Fri, 06 Oct 2017 15:57:02 GMT

Files:

first 1 load

FileBaseName() as filebasename

FROM

[Store - *.xlsx] (ooxml, no labels, table is SheetName);

let v = NoOfRows('Files');

let f = 1;

Do while f <= NoOfRows('Files')

let filename = peek('filebasename', $(f)-1);

// do something with the file (load, concatenate, etc.....)

trace $(f) $(filename);

Let f=f+1;

Loop