QVD concatenation drops data sometimes

[sifatnabil]

Hi all,

I have a batch file which loads a qvw, which queries a DB and concatenates the result to a QVD every hour, and then replaces the original QVD. This works fine most of the time, but occasionally (every few weeks), the task drops data and concatenates the latest load with the QVD as it looked 1 month ago, which means data from 1 month ago to today is missing from the full QVD.

So for example, today (11 Feb 8 am) the batch file ran and concatenated the result with the QVD but from 11 Jan, instead of 11 Feb 7am - so now the date field sequence on the QVD is 11 Jan, 11 Feb, and the dates in between are missing.

Note that this task has been running since 2014 and so it's grown to over 400MB. Could this be why?

[Mark_Little]

Hi,

I think this will take a little trouble shooting the find the problem. You noticed anything that could connect the the times when the load has not completed incorrectly? I,.e. times,dates, week day?

If i was troubleshooting this i would probably start, by keeping the query data as well a the full snap shot, This way we can look at the query qvd, when the issue occurs and see why it isn't concatenating with the full qvd data.

Mark

[gsbeaton]

Hi Sifat,

Sorry my replies are coming in an unusual order, but all my posts are being moderated.

So, a couple of things here.

• reloadtime(), when used in the script, is going to tag your snapshot with the previous time your script ran.  So your snapshots are possibly being tagged incorrectly.
• So when your DB takes a 'snapshot', is it only the records since you last ran the query, or is it all records?
• What are you doing with your 'tagged' snapshots?  Are these views in a datawarehouse, or are the stored QVDs in a file?

It doesn't actually sound as if you are doing a 'concatenate' as understood in Qlik terms, it sounds as if you are doing a flush and reload, ie, getting all records from the DB and replacing your QVD with the new records.  If that is the case, then my strong suspicion would be that the issue is with the SQL code that is being run to pull back the view.

Hope that helps

George

[Anonymous]

As an aside for each reload run you could create a seperate QVD with its name suffixed by reload DateTime and not do the concatenate.

Then in your dashboard qvw do a wildcard QVD load or load them using a loop.

This will make your reload a load quicker re the QVD Load & Store, and could well make your diagnostics of whatever the underlying issue is easier.

[Peter_Cammaert]

Are you sure this is your actual script? Or did you translate/modify some parts into pseudo-code before you posted it in this thread?

If not, here are some minor items you may want to reflect upon:

• LOAD ... from DB; tries to read from a QVD called DB. That's because option (qvd) is the default when none is specified explicitly, and the FROM clause always reads from a file.
• If the LoginPoll.qvd file doesn't exist yet, you create it with double data from file DB; once inside the IF-THEN-ELSE and once during the concatenate.
• The STORE statement doesn't allow for an AS clause except when renaming individual fields. Which is not the case here. I guess it should be INTO instead of AS.

Best,

Peter

[sifatnabil]

gsbeaton To give context, the DB table is a constantly refreshing table of users logged in at at any point in time. If a user logs out, the user is dropped from the table. So when I take snapshots, I query the entire table, and I get a view of who was logged in at that hour. By concatenating these snapshots with each other, I get a growing QVD of users logged in each hour, 24 hours a day. Note that I'm running this as a service from Windows Task Scheduler via a batch file reload. Also, I'm not using ReloadTime(), I'm actually using let ReloadTime = now();

The tagged snapshots are being concatenated with each other into 1 large QVD file. That 1 large QVD file is LoginPoll.qvd that you see in the pseudo-script:

let ReloadTime = now();

If qvdcreatetime('QVDs\LoginPoll.qvd') Then

USLogin:

LOAD

*

FROM [QVDs\LoginPoll.qvd] (qvd);

else

USLogin:

Load

*,

'$(ReloadTime)' as ReloadTime

from DB;

end if

Concatenate(USLogin)

USLogin:

Load

*,

'$(ReloadTime)' as ReloadTime

from DB;

store [USLogin] as into [QVDs\LoginPoll.qvd] (qvd);

Do you have a better alternative to tagging the time? As the table doesn't actually have a timestamp field. Is there a better alternative to let ReloadTime = now(); ?

I think that might be the issue.

[swuehl]

Default timer mode for now() should 1, i.e. function call. But you can make it explicite to be sure:

Let ReloadTime = Now(1);

[gsbeaton · Accepted solution]

I think you are nearly there.  Your pseudo code infers a couple of logic issues, but it may be ok.  Just to be clear though, here are the steps that I think you should be taking:

1. Always load your LoginPoll QVD, no need for an IF() around that, you will need it every time, except for the very first time you ever ran the script (which sounds like it was a few years ago).  If the QVD is not there, you have bigger problems.
2. Put a timestamp in the filenames of all of your snapshots, so they are something like snapshot-2016021114.csv (YYYYMMDDHH)
3. Use the qvdcreatetime() function to get the time the QVD was last updated, then store that in a variable.
4. Use the timestamp from your variable created in step 2 here as a parameter to load the correct snapshots.  Personally I would do this by creating an array of times which you can then loop through in the next step.  When I say array, I mean a parametarised string, eg 2016021110; 2016021111; 2016021112; 2016021113;
5. Load all of your files in from step 4.  So long as they have the same column names as your QVD from step 1, they will automatically concatenate.
6. Store your table and drop from memory.

Another approach would be to use Qlik's wildcard feature to load all the snapshots in the folder, then delete or move them once successfully loaded.   Both can be done from within QlikView.  There's would be a lot less to go wrong with that approach.

Hope that helps

George

[sifatnabil]

Thanks - I'll give this a go.