Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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?
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.
Try like this
Bookmaster:
LOAD
a
b
c
'' as d
From
(ooxml, embedded labels, table is Bookmaster);
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