New to Creating QVD's and have some questions

[#1]

I am learning how to use and create QVD files.

Besides and quicker speed and reduced database use, we want to use them to create a way to do the following:

We have a production management dashboard that I created. This includes workstation load and capacity, worker efficiency, job information, and employees currently working. We want to be able to refresh the data linked to the currently working portion every 5 minutes, but have the rest of the data refreshed twice a day.

The current script contains roughly 32 tables, out of which 5 of those tables are what we need refreshed every 5 minutes.

We thought that creating QVD files for the tables we want to refresh twice daily and replacing them in the script would be a solution. The QVD creating would then happen twice a day, and the tables we want refreshed every 5 minutes would remain on the script and the entire document would have this refresh added to it effectively pulling only the needed tables, and the data from the QVD's.

I have looked into the partial reload, but it seems like it would not work well from the server side with scheduling the reload.

My questions are these:

What would be the best way to create these QVD files? IE: adding the needed tables to a new document and creating all QVD's there (Worried about linking), or just doing qualify loads for all the tables in one QVD and worrying about doing the linking and data reduction in the main document?

To make it a little harder we would also be doing an Incremental Insert, Update, and Delete, so I would need to use the following for each table?

Let ThisExecTime = Now( );
QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(ThisExecTime)#;
Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT EXISTS(PrimaryKey);
Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;
If ScriptErrorCount = 0 then
STORE QV_Table INTO File.QVD;
Let LastExecTime = ThisExecTime;
End If

Other Question: What does this mean by PrimaryKey?

Also attached is a picture of the current table linkage. The circled tables are the tables we want refreshed every 5 minutes.

Any ideas on the best way to proceed?

[richard_chilvers]

Hi Marc

An interesting post and thanks for describing it well. I may be able to help a little as I have done something similar.

Best to consider the 5 minute requirement first. The bigger one should then become clearer perhaps.

I get the idea of your tables but not the details. So let’s assume you have 2 tables:

JOB with fields Job_No and Employee_No

EMPLOYEES with fields Employee_No and Name

Firstly create the base QVD. Load the data into a table in QV (linked by Employee_no of course) and then save it to a QVD.

Next, decide how you know what are ‘new’ records in JOB. For example, if Job_No is sequential, you can identify the new ones as being those with numbers higher than the last one you LOADed into QVD. Or you might have a date/time stamp, although I couldn’t see that in your data.

Your 5-minute QV will LOAD the QVD you created and then add only records which were created since the previous LOAD, as identified above.

I hope this helps a little.

Regards

[#3]

Hi Richard,

So I would create a new document, add the tables I want use a join (How can I maintain the full outer join that is default? I would like all employees even if they are not clocked into jobs.)

Due to the data set being modified, added, or deleted I need to use the Insert, Update, and Delete incremental load which is also kind of confusing. I am now guessing that in the code I originally posted the primary key is the field I need to see if it needs to change the current data.

(I can not use job number due to it being a string, and I need some way to determine if the other fields have changed. Create date can be used to add new records however. I might need to create a new field in the system that will record the current time whenever the file is saved to do the update portion)

Would we then add this new QV document to the server with a reload of 5 mins to keep the QVD updated?

I would then (Instead of adding the tables from the database) add this QVD to my final QV document with the reload set to 5 mins?

[rwunderlich]

If you are new to incremental reloads, let me suggest a couple of script resources.

The QV Cookbook available at http://robwunderlich.com/downloads/ has a working incremental reload example.

The Qlikview Components project http://code.google.com/p/qlikview-components/ provides subroutines that make incremental loads easier.

-Rob

[richard_chilvers]

Hi Marc

As Rob says, there is some good documentation around, but it sounds to me like you have read some of this and have a fairly good grasp of the concept.

To answer your last question first. Once you have created the first version of the QVD (you should only need to do this once, but keep the application because in my experience you might need it!) you just need to run your 5-minute application. This will LOAD from the QVD all records except those which you know have been deleted or updated. And then you will insert records which have been updated or which are new.

If you can use a Date/Time stamp field on the Job records to show when they were last modified, this will be a great way of checking what's changed since the last run of the application (5 minutes or so ago).

Deleted records are a bit more tricky - and I hadn't considered them in my original reply. If you look back at your original example, that's the purpose of the NOT EXISTS. You should be able to use something similar, depending on your data, by comparing what's in the QVD but no longer in your Job table.

Richard