Generating QVDs from multiple files

[ElleBee]

Hi all. New to Qlik and hoping for some help.

I'm trying to create a script that will pull in all of the available xlsx files in the datafiles and generate a QVD. I want to then be able to switch out new files and run it again, changing the QVD name to create multiple QVDs.

I have the following so far: 

sub GetAllExcelSheets (DataFiles)

for each Ext in 'xlsx'

for each File in filelist ('lib://QVD Generators:DataFiles'&'/Apps*.' &Ext)

QVD_Table:
Noconcatenate
LOAD
[Field 1],
[Field 2],
[Field 3]

FROM ['lib://DataFiles'](ooxml, embedded labels);

next File

next Ext

end sub

call GetAllExcelSheets ('lib://(DataFiles)')

store QVD_Table into [lib://DataFiles/Apps_Q1_19-20.qvd] (qvd);

I get the following error message when I run the script:

The following error occurred:

Table 'QVD_Table' not found

The error occurred here:

store QVD_Table into [lib://DataFiles/Apps_Q1_19-20.qvd] (qvd)

Not sure where I'm going wrong. Any help would be appreciated 

[marcus_sommer · Accepted solution]

From isn't correct and should more look like:

FROM [$(File)] (ooxml, embedded labels);

Also the path within the filelist() didn't look right and may rather something like this:

filelist ('lib://QVD Generators/$(DataFiles)/Apps*.$(Ext)')

TRACE $(YourVar); is often quite helpful to see what the variables really contain and if it's in your case a valid path.

- Marcus

[ElleBee]

Hi Marcus,

Corrected from statement now - thank you. 

I've changed the filelist path to ('lib://QVD Generators/$(DataFiles)/Apps*.$(Ext)') but now I get the below error. 

The following error occurred:
Connection not found: QVD Generators (: POST /endpoints failed with status code 404. Error: Connection 'QVD Generators' not found)

The error occurred here:

for each File in filelist ('lib://QVD Generators/lib://(DataFiles)/Apps*.xlsx')

I just want it to look at the files that are in the space which is named "QVD Generators"

[marcus_sommer · Accepted solution]

There are now two libraries specified - therefore try it with:

filelist ('$(DataFiles)/Apps*.$(Ext)')
or
filelist ('DataFiles/Apps*.$(Ext)')

- Marcus