I have a folder where I load all of the files within it to create a historical data set.
As the records are now approaching 10 million lines I have set it to buffer load so it only loads the newly added files and skims quickly over the old files.
I now want to add to this report and concatenate this data into another set of files but when I do concatenate buffer load it doesnt buffer and the 10 million lines take 20 minutes to load.
The code below is uses the * to load all of the files in the folder AllFiles.
I am concatenating a number of these loads (looking at different folders), and I want to buffer the load as each week the folder grows by 4 files and is getting huge.
using a combination of buffer and concatenate means that the buffer is ignored
buffer concatenate LOAD @1 as State, @2 as Location, @3 as Brand, @5 as Product, @6 as Barcode#, @14 as Sales
FROM (ansi, txt, delimiter is '\t', no labels, header is line, msq);
I'm assuming you actually need CONCATENATE, that is, the LOADs from the different folders have different Field names?
I think I understand why BUFFER doesn't work with CONCATENATE. The buffers are QVD files and a buffer load is an optimized qvd load. As others have reported here, CONCATENATE causes an unoptimized load. My guess is that the buffer is not used unless an optimized load can be done.
A suggestion would be to do a standard buffer load for each folder and then concatenate the data with load resident.
To expand on what Rob said, the only reason to use CONCATENATE is in the situation where the number and name of the loaded fields don't match.
For example:
BUFFER LOAD @1 AS Statement , @2 AS Location , @3 AS Brand , @5 AS Product , @6 AS Barcode# , @14 AS Sales FROM (options);
BUFFER CONCATENATE LOAD @1 AS Statement , @2 AS Location , @5 AS Product , @6 AS Barcode# , @14 AS Sales FROM (options);
.. Is a situation where you would need to specify CONCATENATE, since the second load statement is missing the Brand field.
If all the files being loaded have identical columns, then the CONCATENATE should not be necessary, as Qlikview will automatically concatenate in that case.
Just to venture a guess, I believe the reason that Qlikview doesn't buffer when CONCATENATE is used is because of the way the resultant QVD's end up named. The automatic naming is based on the LOAD statement, so the two+ buffered load statements will inherantly buffer to two+ files. When Qlikview sees a forced concatenation, it likely assumes that the two loaded tables are different, and as such would not be compatible to optimize load directly to memory.
I suspect that if you were to drop the CONCATENATE keyword, the buffering and concatenation would work as expected. If the field names and numbers are different, I recommend manually assisting concatenation by using AS to match up field names, and using NULL As FieldName to fill in gaps between the two field sets. That should allow Qlikview to automagically concatenate for you.
