topic Re: Load Files based on data in a Field in QlikView

Load Files based on data in a Field

shane_spencer — Fri, 07 Jul 2017 08:23:37 GMT

I've got a script to loop through a number of folders to load some spreadsheets. Currently I can either loop through all folders

i.e.

for each dir in DirList('..\SOURCE\*')

or loop through specific folders

i.e.

for each dir in 'OCT','NOV'

What I would like to do is load only specific folders based on the contents of a field

i.e.

Please see the attached example of my work so far and some same data. Can anyone please help?

Re: Load Files based on data in a Field

balar025 — Fri, 07 Jul 2017 08:46:23 GMT

Hi ,

P;ease find the below change in code:

FolderList:

LOAD concat(chr(39)&Folders&chr(39),',') as FolderList

FROM [Folders.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let vListOfFolder = Peek('FolderList',0,'FolderList');

for each dir in $(vListOfFolder)

for each datafile in FileList('..\SOURCE\$(dir)\DATA.xlsx')

Monthly:

LOAD *,

// '$(datafile)' as filepath,

'$(dir)' as Month

//FROM [DATA.xlsx]

//FROM [..\SOURCE\$(dir)\*.xlsx]

FROm $(datafile)

(ooxml, embedded labels, table is Sheet1);

next datafile

next dir

Regards,

Ravi Balar

Re: Load Files based on data in a Field

sunny_talwar — Fri, 07 Jul 2017 08:52:59 GMT

You can try this

FOR i = 1 to FieldValueCount('Folders')

LET vFolder = FieldValue('Folders', $(i));

LET datafile = '..\SOURCE\$(vFolder)\DATA.xlsx';

Monthly:

LOAD *,

'$(dir)' as Month

FROM $(datafile)

(ooxml, embedded labels, table is Sheet1);

NEXT i

Re: Load Files based on data in a Field

balar025 — Fri, 07 Jul 2017 09:01:27 GMT

Hi Sunny,

As per observation, in your script it will not handle if folder is not present.

-Ravi Balar

Re: Load Files based on data in a Field

shane_spencer — Fri, 07 Jul 2017 09:28:34 GMT

Thanks - I'll keep this as an example, but I think Ravi's answer is closer to what I was working on myself using concatenate though I was only about 25% there.

Re: Load Files based on data in a Field

sunny_talwar — Fri, 07 Jul 2017 11:05:48 GMT

There are ways around that to make it work.... personally, I used to use Concatenate, but the reason i stopped using Concatenate is because it slows done the reload time. In this case the list to be concatenated is small, so it doesn't matter. But for a longer list (of say 15000 or 50000), its better to use FieldValue functions instead of Concat.

Re: Load Files based on data in a Field

sunny_talwar — Fri, 07 Jul 2017 11:07:01 GMT

Like I mentioned above, for your data it might not make a difference, but FieldValue is def. something you would want to use because of its capability to look at DISTINCT values outside of a table .

Best,

Sunny

Re: Load Files based on data in a Field

shane_spencer — Fri, 07 Jul 2017 11:58:05 GMT

Thank you. And thank you for the explanation of advantages too!

Re: Load Files based on data in a Field

sunny_talwar — Fri, 07 Jul 2017 11:58:30 GMT

No problem at all