topic Re: Add a column to existing QVD data in QlikView

Add a column to existing QVD data

Anonymous — Fri, 01 Jun 2018 09:12:39 GMT

Hi,

My current process is to load an excel file called 'Bookmaster' - Qlik then stores as a QVD and drops the excel file before reloading the 'BookmasterHistoric' QVD files.

What would be the best process for adding a new column to the Bookmaster - and pulling this into BookmasterHistoric from this day forward?

Re: Add a column to existing QVD data

shiveshsingh — Fri, 01 Jun 2018 09:41:04 GMT

What is the logic for adding new column?

Is it a row number or date? Can you share data to work upon along with logic?

Re: Add a column to existing QVD data

Anonymous — Fri, 01 Jun 2018 10:29:12 GMT

Bookmaster:
LOAD ,

d **new column

From

(ooxml, embedded labels, table is Bookmaster);

Let vDate = Date(Today(), 'DD-MM-YYYY');
Store Bookmaster into **Bookmaster_$(vDate).QVD(QVD);

DROP Table Bookmaster;

BookmasterHistoric:
LOAD
filename() as FileName,
date(date#(mid(filename(), 12, 10), 'DD-MM-YYYY'), 'DD-MM-YYYY') as OrderDate,

FROM
[**Qlik Historic\Bookmaster_*.QVD]
(qvd);

I want to be able to add a new column to Bookmaster excel and join it into Bookmasterhistoric. In aboves example, I want to pull new 'D' into the QVD files going forward, even though it wont have a value in any previous loads.

Re: Add a column to existing QVD data

shiveshsingh — Fri, 01 Jun 2018 16:13:14 GMT

Try like this

Bookmaster:
LOAD

'' as d

From

(ooxml, embedded labels, table is Bookmaster);

Re: Add a column to existing QVD data

rwunderlich — Fri, 01 Jun 2018 19:15:05 GMT

If you modify your historic load to use Load *, you'll pick up the new column where it exists but tolerate the fact that it is not present in all files.

To force the QVDs with unlike columns to load as a single table, you'll need the Concatenate keyword. Because the table does not exist before the wildcard load, you'll need to add a little trick to load and then later drop a dummy field.

BookmasterHistoric:

LOAD 0 as DummyField AutoGenerate 0;

Concatenate (BookmasterHistoric)

LOAD

filename() as FileName,

date(date#(mid(filename(), 12, 10), 'DD-MM-YYYY'), 'DD-MM-YYYY') as OrderDate,

FROM

[**Qlik Historic\Bookmaster_*.QVD]

(qvd);

Drop Field DummyField;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com