topic Re: Looping through files applying stored variable in QlikView

Looping through files applying stored variable

Wed, 24 Sep 2014 16:04:18 GMT

Hi Folks,

I have little experience with Looping. Your help will be much appreciated. I need to loop through each excel document and each document has a different ForEx rate. This script works well for one file but I need it to loop through the others capturing their individual ForEx rates. Thanks in advance!!

ForEx:

LOAD

ForEx

FROM 'Data_CA.xls';

Let ForEx = num(peek('ForEx',-1,ForEx),'#.######');

Drop Table ForEx;

Table:

Load

Column1,

Column2 * $(ForEx) as Column2

FROM 'Path\Data_CA.xls';

But I need to loop through multiple excel files so the FROM would read 'Path\Data*.xls'

Re: Looping through files applying stored variable

Colin-Albert — Wed, 24 Sep 2014 16:07:23 GMT

Table:

Load

Column1,

Column2 * $(ForEx) as Column2

FROM 'Path\Data*.xls'

;

This should load all spreadsheets, provided the sheet names, header rows and column names etc are the same in all your spreadsheets

Re: Looping through files applying stored variable

Wed, 24 Sep 2014 16:10:36 GMT

The columns are the same name. But I need to loop through each file, capturing and applying their appropriate ForEx rates.

Re: Looping through files applying stored variable

Anonymous — Wed, 24 Sep 2014 17:55:41 GMT

As Colin suggested , load all files to the table and then use your calculation for the column

Table:

Load

Column1,

Column2 ,

ForEx

FROM 'Path\Data*.xls';

Table2:

Load

Column1,

Column2 * ForEx as Column2

Resident Table;

Drop Table Table;

Re: Looping through files applying stored variable

maxgro — Wed, 24 Sep 2014 19:44:16 GMT

..........

set vPath='C:\Users\mgrossi\Downloads\Data*.xlsx'; // xlsx

table: load '' as dropme autogenerate 0;

For Each vFile in FileList('$(vPath)')

concatenate(table)

LOAD

Column1,

Column2 * $(ForEx) as Column2

From [$(vFile)]

(ooxml, embedded labels, table is Sheet1); // change for xls

next;

drop field dropme;