concatenate QVD with different columns

[viveksingh]

Hi All,

I have multiple QVDs with date in qvd name ex: Sales_20200901, Sales_20200902......
alos, I have another set of QVDs with similar names ex: Sales_20200903, Sales_20200904...

first two qvds Sales_20200901, Sales_20200902 will have 5 columns
second two qvds Sales_20200903, Sales_20200904 will have same column names and few extra columns
Now I want to load All qvds and concatenate together.

Load
*
from Sales_*.qvd(qvd);

The above code creates sys keys as it considers as multiple qvds

expectation is to concatenate all QVD even if the columns are different when we load with wild cards. 

[Oleg_Troyansky · Accepted solution]

Hi,

If you create an empty table prior to the load, then you can use the CONCATENATE keyword to force concatenation into the same table:

Sales:
load 1 as Dummy autogenerate 1;

concatenate (Sales)
LOAD * FROM Sales*.qvd (qvd);

Drop field Dummy;

--------------

You will end up with a single empty row that wasn't part of your data. If this is unacceptable, then you can load the QVD files in a loop one by one, using the syntax FOR EACH, as described here:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip... 

Cheers,

[PrashantSangle]

Just adding to solution of Oleg sir. If you don't want that empty column then using resident you can remove that row and then drop dummy field

like

Sales:
load 1 as Dummy autogenerate 1;

concatenate (Sales)
LOAD * FROM Sales*.qvd (qvd);

noconcatenate

sales_final:

Load * Resident Sales where dummy <>'1';

drop table Sales;

Drop field Dummy;

Thanks & Regards,

Prashant Sangle

[marcus_sommer]

Just another addition:

load 1 as Dummy autogenerate 0;

- Marcus