Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
shane_spencer
Valued Contributor

Load Files based on data in a Field

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.

folders.PNG

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

1 Solution

Accepted Solutions
balar025
Contributor III

Re: Load Files based on data in a Field

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

8 Replies
balar025
Contributor III

Re: Load Files based on data in a Field

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

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

balar025
Contributor III

Re: Load Files based on data in a Field

Hi Sunny,

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

-Ravi Balar

shane_spencer
Valued Contributor

Re: Load Files based on data in a Field

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

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

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

shane_spencer
Valued Contributor

Re: Load Files based on data in a Field

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

Re: Load Files based on data in a Field

No problem at all