Append Data to An Existing QVD

[wonkymeister]

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

[Gysbert_Wassenaar · Accepted solution]

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

[wonkymeister]

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

[Gysbert_Wassenaar]

See my post above.

[wonkymeister]

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!

[Gysbert_Wassenaar]

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.

[wonkymeister]

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!

[Gysbert_Wassenaar]

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.

[wonkymeister]

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