Add a column to existing QVD data

[Anonymous]

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?

[shiveshsingh]

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?

[Anonymous]

Bookmaster:
LOAD , 

a

b

c

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,

a

b

c

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.

[shiveshsingh]

Try like this

Bookmaster:
LOAD

a

b

c

'' as d

From

(ooxml, embedded labels, table is Bookmaster);

[rwunderlich]

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