topic Re: Add new column to QVD in QlikView

Add new column to QVD

balanandam — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a new column added to my source database, and that should be pulled out to qlikview and append to existing qvd. what is the best way to do this ?

I know the method, run full extract and create qvd starting onwards, but the data size is in very big that takes hours to complete this. As now I am loading data incremental manner to qlikview. Pls. suggest.

Br, Balanandam

Re: Add new column to QVD

shiveshsingh — Thu, 08 Mar 2018 05:58:36 GMT

Is that column also having values corresponding to previous data?

Re: Add new column to QVD

big_dreams — Thu, 08 Mar 2018 06:00:17 GMT

Yes you have to run full extractor. There is no alternate way.

Regards

Re: Add new column to QVD

jonathandienst — Thu, 08 Mar 2018 06:04:45 GMT

If the new column is based only on other columns already in the QVD, then it will not be necessary to run the full reload. Create a simple model that loads the qvd with the calculated column and store the qvd

Data:

LOAD *,

<new derived column calculation> as NewColumn

FROM $(vqvdPath)\YourData.qvd (qvd);

STORE Data into $(vqvdPath)\YourData.qvd (qvd);

Re: Add new column to QVD

balanandam — Thu, 08 Mar 2018 06:09:04 GMT

No, this column has created recently and start logging data.

Br, Balanandam

Re: Add new column to QVD

jonathandienst — Thu, 08 Mar 2018 06:13:38 GMT

How are the rows in the new column related to the existing rows?

Re: Add new column to QVD

akshaye_c_navale — Thu, 08 Mar 2018 06:37:19 GMT

You can modify existing QVD by adding new Column and set its default value either blank or NULL.

LOAD *,

<new column > as NewColumnName

FROM $(vQVDPath)\Data.qvd (qvd);

STORE Data into $(vQVDPath)\Data.qvd (qvd);

DROP TABLE Data

Re: Add new column to QVD

qlik4asif — Thu, 08 Mar 2018 07:16:28 GMT

what i understood is you have a QVD,and new column was inserted in DB,

now you want to extract this column from DB and append it to existing QVD.

How abt this way!!!!!!!!!

QVD: //Loading existing QVD

LOAD *

FROM $(vqvdPath)\YourData.qvd (qvd);

Join //new column added to qvd w.r.t to commonfield(Key)

NewField:

Load <Commonfield>,

From DB;

STORE QVD into $(vqvdPath)\YourData.qvd (qvd);

Re: Add new column to QVD

akshaye_c_navale — Thu, 08 Mar 2018 07:27:42 GMT

You don't need to used Join.

If you want to bring New columns data for Historical data also then better perform Full data load.

Re: Add new column to QVD

Akina0929 — Thu, 08 Mar 2018 07:30:19 GMT

use partial Reload it is used to load some tables in data model instead of loading all tables.

Re: Add new column to QVD

chrishayes — Thu, 08 Mar 2018 19:55:47 GMT

From what I've read here so far, it seems the column was added to a table in the source system, but the existing rows should have no value. Only new rows created will have a value. Update your existing incremental load script to accommodate the new column in the source table. Create a one-time QVD loader that loads the existing QVD and adds the new column:

LOAD

Null() as New_Column

FROM

MyData.qvd

;

Write out the QVD with the new column as all nulls. Then you can deploy your modified incremental load script and new and updated rows will come in with the new column populated.

Re: Add new column to QVD

rwunderlich — Fri, 09 Mar 2018 02:26:20 GMT

Just simply add the new column to your extract. When you merge with the existing QVD, use the Concatenate keyword on the Load statement.

Concatenate (Facts)

LOAD * facts.qvd

where not exists(key);

STORE Facts into....

You can remove the Concatenate prefix after one run if desired.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Add new column to QVD

big_dreams — Fri, 09 Mar 2018 17:17:59 GMT

Hi Sir,

with all due respect, I don't get it how it can be optimize way to achieve it.

1st thing we can not use exist() in database query for that we have to load all data from database to temp qvd then

concatenate it to existing qvd using not exist. So if we are loading all data in temp qvd then why we are not using directly.

might there is some thing in your logic which i don't get it. Can you explain it little bit more. I am interested to learn it.

Sorry if I say anything wrong.

Regards,

Re: Add new column to QVD

rwunderlich — Fri, 09 Mar 2018 22:12:38 GMT

The original poster stated that he was using incremental load so I assume he had a QVD that held the rows to date. The new rows, picked up by incremental load, will have the new column. I was suggesting that he did not need to worry about adding the new column to the existing rows in the QVD. QV will assign nulls values to the existing rows automatically.

-Rob

Re: Add new column to QVD

big_dreams — Sat, 10 Mar 2018 02:20:22 GMT

got it sir..

Re: Add new column to QVD

phapalesaurabh — Mon, 24 Jun 2019 07:30:06 GMT

@rwunderlich ,

Is der a way remove to Columns from Qvd Just like this Trick ?

Or Simply to create a model which will drop columns from Qvd ?

I have a requirement to drop a column which is going to be removed from Database and Our data loads are incremental in Qlik.

Re: Add new column to QVD

rwunderlich — Wed, 26 Jun 2019 03:00:54 GMT

You can either not load the field when you load the existing QVD or "Load *" followed by "DROP Field X'.

-Rob