Regularly in a QlikView application, you need to load from a directory or set of identical files which simply have different file names but contain the same type of data. For example, log files generated by systems contain the same type of data in a set structure, but the filenames often reflect the time and date or the system the log file was generated from.
In this blog, I will show you how to (really simply) load in to QlikView, a set of files that are identical in makeup but are not named the same.
So, imagine we have a directory full of log files on your server as below...
I need to load all of these log files in to my application, but I do not want a single load statement in my script for each of the 100+ files. So, firstly we need to perform a standard load from our table (.log) files. When you have done this using the qlikview script editor, you will have a simple load statement...
LOAD Sequence#, Timestamp, Level, Hostname, Logger, Thread, Id, User, Exe_Type FROM [vm-qvs12__Application_QVS_2012-12-04T13.25.38Z.log] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
So this statement would load all of the columns listed from the specific file in the FROM statement. However, we want to utilise one single LOAD statement for all of our log files. By simply changing the filename to contain an asterisk, provided all of the columns that you are placing in your load statement exist in all of your files, using an asterisk will cycle through all of the .log files that start with the text "vm-qvs12__Application_QVS_" in the directory, and load the data in to your application.
FROM [files\Log\QlikViewServer\vm-qvs12__Application_QVS_*.log] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
Now we have all of the data in our application, we may want to know from which log file the data in the application came from. So, we can use some functions to give us some extra data.
We can use the Filebasename() function to give us a column in the data that contains the filename of the loaded log file.
LOAD filebasename() as SourceFilename, Sequence#, Timestamp, Level, Hostname, Logger, Thread, Id, User, Exe_Type FROM [files\Log\QlikViewServer\vm-qvs12__Application_QVS_*.log] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
Now, that was a simple example of the wildcard load. What if I wanted to load data from multiple files and enhance the script with something like a preceding load? To do this I would have to utilise a simple function/variable and a small loop. In the example below, you can see that I am loading from multiple xlsx files, again using the filebasename function, but I am also creating a column called Total in my preceding load. The For Each and Next keywords tell QlikView to loop through the files in the location that conform to the wildcard in the Filelist function.
For eachvFile in FileList('C:\Users\aby\Desktop\*wildcard.xlsx')
Load Col2+Col3 as Total, *; Load *, Filebasename() as Source from [$(vFile)] (ooxml, embedded labels, table is Sheet1);
Hopefully I have managed to simplify some of the scripts you use today!