topic Re: Append Data to An Existing QVD in QlikView

Append Data to An Existing QVD

wonkymeister — Wed, 12 Nov 2014 12:53:31 GMT

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

Re: Append Data to An Existing QVD

Gysbert_Wassenaar — Wed, 12 Nov 2014 13:03:26 GMT

// 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);

Re: Append Data to An Existing QVD

wonkymeister — Wed, 12 Nov 2014 13:11:19 GMT

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

Re: Append Data to An Existing QVD

Gysbert_Wassenaar — Wed, 12 Nov 2014 13:16:00 GMT

See my post above.

Re: Append Data to An Existing QVD

wonkymeister — Wed, 12 Nov 2014 13:43:28 GMT

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

Gysbert_Wassenaar — Wed, 12 Nov 2014 13:47:48 GMT

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.

Re: Append Data to An Existing QVD

wonkymeister — Wed, 12 Nov 2014 14:29:00 GMT

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

Gysbert_Wassenaar — Wed, 12 Nov 2014 14:37:28 GMT

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.

Re: Append Data to An Existing QVD

wonkymeister — Wed, 12 Nov 2014 14:55:20 GMT

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