Sunday, July 3, 2011

Loading Multiple Files With Varying Fields

If you ever have a need to load multiple files into a single QlikView table and the files have similar but not identical fields then you might try the solution below. I’ve used this method where the files are historical data created at varying times and although the files have most fields in common, some of the fields have extra fields. For example, in a set of sales data files, the files for exported products might have extra fields for export license data.

Here is an example of the code from the loadscript. It loads a set of QVD files. It loads the first file normally, then loads all subsequent files using the Concatenate keyword. This allows the data, including the extra fields, to be loaded into the same QlikView table, SALES_TABLE.

set filectr=0;
for each sfile in filelist (e:\sales_history\QVD_SALES_MONTH*.qvd)
  If filectr=0 then
   Load * from $(sfile) (qvd);
   Concatenate Load * from $(sfile) (qvd);
  Let filectr=$(filectr)+1;
next sfile

★ ★ ★

1 comment:

Steve Dark said...

This is a nice solution, but obviously forces a non optimized QVD load.

IMHO there is no real substitute for planning ahead and adding null columns into QVDs as required so a single load from a wildcard can be performed.