Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

wonkymeister
Contributor

Append Data to An Existing QVD

Hi,

I’m having an awful lot of trouble to do something that on paper seems very simple. I wish to append the data from one QVD to another.

QVD (A) runs daily and only ever contains 1 days worth of data (its SQL source is truncated daily). I wish to append this data daily to a new QVD (B)

And I only ever want to append the data to B from A that doesn’t already exist in B.

If I can get this working QVD B will allow me to trend my data as it’ll store data by the day.

So far I’ve got the following but I don’t think it’s appending correctly, and I also need the NOT EXISTS statement which im not sure how to apply as I cant do a simple test on the Primary Key (I need to test the entire row of data to check if it already exists).

QVDA:

LOAD * From $(vMyPath)QVDA.qvd(qvd);

QVDB:

CONCATENATE(QVDA)

LOAD * From $(vMyPath)QVDB.qvd(qvd);

Anyone offer any advice/help?

cheers

Tags (1)
1 Solution

Accepted Solutions

Re: Append Data to An Existing QVD

// One time action; comment out the next three lines after you've added the KEY field to the QVDA table

QVDA:

LOAD *, autonumberhash256( Field1, ... , FieldX) as KEY From $(vMyPath)QVDA.qvd(qvd);

STORE QVDA INTO $(vMyPath)QVDA.qvd(qvd);

QVDA:

LOAD * From $(vMyPath)QVDA.qvd(qvd);

QVDB:

CONCATENATE(QVDA)

LOAD *, autonumberhash256( Field1, ... , FieldX) as KEY From $(vMyPath)QVDB.qvd(qvd)

WHERE NOT EXISTS(KEY, autonumberhash256( Field1, ... , FieldX) );

STORE QVDA INTO $(vMyPath)QVDA.qvd(qvd);


talk is cheap, supply exceeds demand
8 Replies

Re: Append Data to An Existing QVD

// One time action; comment out the next three lines after you've added the KEY field to the QVDA table

QVDA:

LOAD *, autonumberhash256( Field1, ... , FieldX) as KEY From $(vMyPath)QVDA.qvd(qvd);

STORE QVDA INTO $(vMyPath)QVDA.qvd(qvd);

QVDA:

LOAD * From $(vMyPath)QVDA.qvd(qvd);

QVDB:

CONCATENATE(QVDA)

LOAD *, autonumberhash256( Field1, ... , FieldX) as KEY From $(vMyPath)QVDB.qvd(qvd)

WHERE NOT EXISTS(KEY, autonumberhash256( Field1, ... , FieldX) );

STORE QVDA INTO $(vMyPath)QVDA.qvd(qvd);


talk is cheap, supply exceeds demand
wonkymeister
Contributor

Re: Append Data to An Existing QVD

any idea how i test each row of data to see if it already exists?

Re: Append Data to An Existing QVD

See my post above.


talk is cheap, supply exceeds demand
wonkymeister
Contributor

Re: Append Data to An Existing QVD

cheers - it works although its doubled up my numbers for some reason.

i'll double check the code for typo's

thanks for the help Gysbert!

Re: Append Data to An Existing QVD

The doubling is probably the result of not dropping the QVDA table the first time after the first KEY creating load statement. The result is that the data from the QVDA.qvd file is loaded twice then.


talk is cheap, supply exceeds demand
wonkymeister
Contributor

Re: Append Data to An Existing QVD

Gysbert you're a star mate - works a treat.

numbers are correct now that i drop the initial table - test tomorrow when the main dataset runs!

thanks again!

Re: Append Data to An Existing QVD

Keep in mind that you only need to create the KEY field in the QVDA once. After the first run you need to comment out (or remove) that first load statement. After that the QVDB load statement creates the KEY field for the new records.


talk is cheap, supply exceeds demand
wonkymeister
Contributor

Re: Append Data to An Existing QVD

yep - done that. i guess as the KEY's stored in the QVD after the initial load/store?

will test tomorrow. i have another QVD that i wish to apply this logic too also so will do that now.

its nice to get away from SQL/data warehouse and use the power of QV!

thanks again

Community Browser