topic Re: concatenate QVD with different columns in QlikView

concatenate QVD with different columns

viveksingh — Fri, 15 Nov 2024 23:59:20 GMT

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.

Re: concatenate QVD with different columns

Oleg_Troyansky — Mon, 14 Sep 2020 22:26:43 GMT

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/ScriptControlStatements/For%20Each.htm

Cheers,

Re: concatenate QVD with different columns

PrashantSangle — Tue, 15 Sep 2020 04:58:21 GMT

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

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

Re: concatenate QVD with different columns

marcus_sommer — Tue, 15 Sep 2020 13:07:43 GMT

Just another addition:

load 1 as Dummy autogenerate 0;

- Marcus