topic Re: QVD concatenation drops data sometimes in QlikView

QVD concatenation drops data sometimes

sifatnabil — Thu, 11 Feb 2016 10:05:34 GMT

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?

Re: QVD concatenation drops data sometimes

swuehl — Thu, 11 Feb 2016 10:09:50 GMT

Are you holding a copy of each hourly created QVD and decide with some logic which historic QVD to load?

Or are you using any other time based logic in your script (in short, please post your script)?

Re: QVD concatenation drops data sometimes

sifatnabil — Thu, 11 Feb 2016 10:18:52 GMT

Hi, the entire task generates only 1 QVD which keeps growing in size due to hourly concatenations to itself.

Here's the simplified algo, which runs hourly.

If exists historicalQVD, then

load historical QVD

else [query DB];

concatenate

[query DB];

store full result as historicalQVD;

Re: QVD concatenation drops data sometimes

swuehl — Thu, 11 Feb 2016 10:37:31 GMT

I assume this is an incremental approach, so you are looking for the max datetime in the historicalQVD, then filter the query DB to only fetch datetime > max QVD datetime, right?

Is the issue always similar, like exactely one month is missing?

Are you tracing that max found QVD date time to logfile?

Or do you know rows written to QVD vs. rows read in the subsequent run?

Any more specific observations?

Re: QVD concatenation drops data sometimes

gsbeaton — Thu, 11 Feb 2016 10:38:38 GMT

Hi Sifat,

I don't have a definite answer, but a few of pointers that may help:

There is no maximum size for a QVD, 400mb is relatively small, I'm working with QVDs of >15gb with no issues.
The QVD is locked while reading and writing
The STORE process in itself will not drop or truncate fields.

My hunch would be that there is something in your script logic that is causing you to lose records. It may take a bit of forensic analysis to find out what it is though. If you want to post more of your script here, then I'd be happy to take a look and help further.

Good luck

George

Re: QVD concatenation drops data sometimes

sifatnabil — Thu, 11 Feb 2016 10:45:13 GMT

I'm actually not looking at the max date time, or enforcing any rule while concatenating. And the issue isn't similar, sometimes it will drop 20 days or 10 days of data. This time for example, the size of the historical QVD dropped from 400MB to 370MB, so it dropped 30MB of data.

Basically, the DB query takes a "snapshot" of the DB table every hour, and I use reloadtime() to generate the timestamp/date to tag the snapshot. It then contenates that snapshot with the historical snapshots. So:

On 11 Jan 8am, the fields will be 8:00:00 11 Jan, field1, field2, field3

On 11 Jan 9am, the fields will be 9:00:00 11 Jan, field1, field2, field3

On 11 Jan 10am, the fields will be 10:00:00 11 Jan, field1, field2, field3

Now when I look at the historical QVD, I can see a larger set of data showing all data relating to 8am, 9am, 10am, etc.

So I don't use max date or anything like that. Just a blunt concatenate.

Re: QVD concatenation drops data sometimes

Anonymous — Thu, 11 Feb 2016 10:51:39 GMT

Can you share the load script ?

Re: QVD concatenation drops data sometimes

sifatnabil — Thu, 11 Feb 2016 10:52:17 GMT

swuehl‌

Here's the actual 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 [QVDs\LoginPoll.qvd] (qvd);

Re: QVD concatenation drops data sometimes

swuehl — Thu, 11 Feb 2016 10:54:15 GMT

Sorry, I don't have any specific idea then.

If you think that size may matter, does your machine hit the limits when you reload? Which version of QV, OS and amount of RAM are you using?

To trace down the issue, could you add some TRACES to e.g. log the last timestamp of the historic QVD when read in (using Peek() ), to see if the issue arise from reading the QVD or concatenating the new data?

Re: QVD concatenation drops data sometimes

sifatnabil — Thu, 11 Feb 2016 10:57:08 GMT

I see..actually I noticed that on Task Scheduler on the server (Windows 2012 R2), during the load, the CPU goes up to 100% momentarily while loading/writing the QVD, which is around 17 million rows now.

Re: QVD concatenation drops data sometimes

Mark_Little — Thu, 11 Feb 2016 10:58:27 GMT

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

Re: QVD concatenation drops data sometimes

gsbeaton — Thu, 11 Feb 2016 11:03:35 GMT

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

Re: QVD concatenation drops data sometimes

Anonymous — Thu, 11 Feb 2016 11:15:43 GMT

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.

Re: QVD concatenation drops data sometimes

Peter_Cammaert — Thu, 11 Feb 2016 11:31:21 GMT

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

Re: QVD concatenation drops data sometimes

sifatnabil — Thu, 11 Feb 2016 13:35:39 GMT

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.

Re: QVD concatenation drops data sometimes

swuehl — Thu, 11 Feb 2016 13:49:26 GMT

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

Let ReloadTime = Now(1);

Re: QVD concatenation drops data sometimes

gsbeaton — Thu, 11 Feb 2016 14:49:50 GMT

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:

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.
Put a timestamp in the filenames of all of your snapshots, so they are something like snapshot-2016021114.csv (YYYYMMDDHH)
Use the qvdcreatetime() function to get the time the QVD was last updated, then store that in a variable.
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;
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.
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

Re: QVD concatenation drops data sometimes

sifatnabil — Thu, 11 Feb 2016 14:58:24 GMT

Thanks - I'll give this a go.