QVD Concatenate reload with replace

[#1]

Hello All,

I am having trouble trying to load data into a .qvd file and then concatenate additional data month to month. My problem is that occassionally the database will be restored back to a prior month and I need to ensure that the data that is newer than the restored database is purged and/or replaces on the reload.

Please let me know any thoughts.

Thanks

Marc

[#2]

Hi Marc,

Before fetching data from Databse, Execute a Select statment using which you can fetch the information which tells you when your data was lastly updated. Based on this factor you can decide whether you can Load/Refresh you QVD or not.

Regards,

Amit

[rwunderlich]

Sounds like you are doing an incremental load like.

1. Load rows from DB
2. Concatenate with QVD

Can you get the maxdate from the DB rows, and then use that in a where clause in step 2 to load QVD rows that are <= maxdate?

-Rob

[#4]

Thanks Amit,

I guess I am wondering if it is possible to just reload or replace part of the data inside the .qvd file or do I need to reload the entire file if and when a database restore occurs.

Thanks

Marc

[#5]

Rob,

That is exactly what I am trying to do only I want to be able to replace or purge some of the data out of the .qvd file when the database is restored.

Thanks

Marc

[rwunderlich]

Marc,

Do you have what you need now? Or do you need a specific example.

-Rob

[#7]

Rob,

Sorry to be a pain but it would be very helpful if you could show me a specific example.

Thanks

Marc

[rwunderlich · Accepted solution]

No worries. Here's a rough sketch and not syntax checked. For anything more complete, please post your existing script.

mydata:
LOAD Key, Date, xxx;
SQL SELECT * FROM mytable;

maxdate:
LOAD max(Date) as maxdate RESIDENT mydata;
LET vMaxdate = peek('maxdate');
DROP TABLE maxdate;

// Concatenate with existing QVD
LOAD * FROM myqvd.qvd (qvd)
WHERE NOT exists(Key)
AND Date <= $(vMaxdate);

If it's not working as expected, be sure to take a look at the logfile to see what is getting generated for vMaxdate.

-Rob

[Bill_Britt]

Marc,

I hope this comes through. Here is a PDF that explains how to do it.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0842.Simple-Incremental-Load.pdf:550:0]

[#10]

Thank you all for your help.

Marc