Skip to content

Instantly share code, notes, and snippets.

@mountaindude
Last active November 17, 2022 23:11
Show Gist options
  • Save mountaindude/fbfefce78ac5e3ba4d5a to your computer and use it in GitHub Desktop.
Save mountaindude/fbfefce78ac5e3ba4d5a to your computer and use it in GitHub Desktop.
QlikView / Qlik Sense code to create table with all QVDs to load later in a script
// List all QVDs that should be loaded
// In this example the QVDs have a filename format of YYYYMMDD.qvd
//
// The rationale for loading QVDs this way, rather than using something like
// load "/some/path/*.qvd" where date > SomeDate
// is that the latter will have to test the date field of all lines within all QVDs in that directory.
// That might take a long time (and it will become slower over time, if new QVDs are added every day), whereas the method
// used in the code below offers (very close to) constant loading time.
//
For each Filename in 'D:\path\to\qvds'
sub DoDir (Root)
for each Ext in 'qvd'
for each File in filelist (Root&'\*.' &Ext)
QVD_Files:
Load left(right('$(File)',12),8) as QVDName,
FileSize( '$(File)' ) as QVDSize,
FileTime( '$(File)' ) as QVDFileTime
autogenerate 1;
next File
next Ext
// Uncomment if you need to include QVDs in subdirectories
// for each Dir in dirlist (Root&'\*' )
// call DoDir (Dir)
// next Dir
end sub
call DoDir ('$(Filename)')
next
// Only include dates later than a cutoff date. Only needed if a subset of the QVDs are to be loaded.
loadDates:
NoConcatenate Load
QVDName as loadDate
Resident QVD_Files
Where
QVDName >= $(vCutOffDate)
;
Drop Table QVD_Files;
//....
//....
// Now load the data from QVDs (or other source file format, if needed)
NumberOfFiles:
NoConcatenate LOAD
NoOfRows('loadDates') as NoOfFiles
Autogenerate(1);
Let vNoOfFiles = peek('NoOfFiles',-1,'NumberOfFiles');
// Loop through list QVDs that should be loaded
For i = 0 to $(vNoOfFiles)-1
let vFileName = peek('loadDate',$(i),'loadDates');
trace vFileName= $(vFileName);
fact_Table:
LOAD
*
FROM [lib://<datasource name>/$(vFileName).QVD]
(qvd)
;
next i
drop table NumberOfFiles;
@martinfcurtis
Copy link

Thanks a bunch for dropping this in GITHUB!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment