topic Re: Can it be done??? in QlikView

Can it be done???

MalcolmCICWF — Wed, 13 Jun 2012 21:15:13 GMT

I had this random idea the other day when one of the companies qvds finished loading but encountered a problem causing only half the records to load. The job did not fail and I knew nothing about it until I tried to call upon that qvd for some data.

My question is, can you create a qvd that would check the record total loaded in another qvd, then check that against the last day or 2 for a percentage/volume drop, and then alert you somehow? It seems that sometimes the load of a qvd does not work and yet you dont notice for some time. I just want some sort of early detection system. Ideas?

Re: Can it be done???

erichshiino — Thu, 14 Jun 2012 01:08:58 GMT

Hi,

This is very interesting! I hope more people will post in this thread.

If you are using incremental reloads, you could leave all the store commands to the end, so if there is an error in the reload. This way, you wouldn't have some qvds updates and the others left behind in case of an error.

You could also use some script functions to read the number of rows in your load and store this number in a QVD.

After this, you could use a qvw to analyse the evolution of your reloads or you could create some controls within the script to read this QVD back and maybe interrupt the script in case your new reload has strange results compared to the past.

I attached a QVW that store the number of rows in a QVD with the timestamp of the reload

Just reload it a couple of times to see the results

Best Regards,

Erich Shiino

Re: Can it be done???

MalcolmCICWF — Thu, 14 Jun 2012 19:30:14 GMT

Thank you, I like the suggestions, I will try to incorporate them soon and see how they work.

Re: Can it be done???

MalcolmCICWF — Thu, 14 Jun 2012 21:37:20 GMT

I will be honest, I do not understand the majority of the first half of this code and what it is doing. How do I adjust to pull my own QVD counts in and multiple if need be? I messed around with it a bit but with now luck. Will it require commenting out Size qvd referenced until it is created with some data?

Re: Can it be done???

erichshiino — Fri, 15 Jun 2012 02:40:51 GMT

Hi,

I'm sorry the first half was confusing.

It can be generated automatically by QV.

In the script editor, hold Ctrl and press Q twice quickly ( Ctrl+Q+Q). This will generate that code. It's intereting to use it as an example because it generates different data everytime you run it.

The real important part of the code is this (I commented to explain the steps:

noOfRecords = NoOfRows('Transactions') ; //get the number of rows from table Transactions
reloadTime = now(1);  //Store the current timestamp on a variable
//It will store the variables above in a table (It generates a single line) 
Size:
Load 'Transaction' as Table, //Store the name of the table you are analyzing - this way you can store multiple tables
    '$(reloadTime)' as reloadTime,
    '$(noOfRecords)'  as noOfRecords 
AutoGenerate (1); 
if QvdNoOfRecords('Size.qvd') > 0 then // This checks if the QVD already exists ( if it exists there is at least one line ther)
//If it exists, load it! Since the field names are the same, it will be concatenated with table 'Size' above
    LOAD Table, 
         reloadTime, 
         noOfRecords
    FROM
    Size.qvd
    (qvd);
end if
//Now that the table size was created (or concatenated), store it back! 
STORE Size into Size.qvd;

So, with this, you can keep track of multiple tables, and the file size.qvd doesn't need to exists the first time you run it.

This piece of script will just keep track of the volumes, after this you still need to implement some controls depending on the type of errors you experienced.

Hope it makes more sense.

Regards,

Erich

Re: Can it be done???

Fri, 15 Jun 2012 03:19:32 GMT

As far as I know, there is menu in QV personal edition in Tools Menu, that you can put some alert.

Which you can set for the onpostreload event, or the others event.

So when the reload successful it will send an email to yours email address.

Re: Can it be done???

MalcolmCICWF — Fri, 15 Jun 2012 14:34:47 GMT

So I am trying to incorporate a QVD we currently load, I do not however want to overwrite anything on this QVD and I fear I will do that. Below is what I was trying to run, but I keep getting the message that QVD_BFRAME_ATTEMPTS does not exist. Can you help me figure out what I need to change around to load the record count of our Bframe Attempts QVD?

Re: Can it be done???

erichshiino — Fri, 15 Jun 2012 15:25:12 GMT

Hi, Can you share the rest of your script? It would be easier to help you

I'm not sure if the QVD_BFRAME_ATTEMPTS is your QVD with data so it might be overwritting it with the script.

you probably need to change the line

noOfRecord = NoOfRows('Transactions') to point it to the table you need to track;

noOfRecord = NoOfRows('YourTableHere')

Inside the if, the code would be:

Load QVD_BFRAME_ATTEMPTS, reloadTime, noOfRecords

Regards,

Erich

Re: Can it be done???

MalcolmCICWF — Fri, 15 Jun 2012 17:28:19 GMT

The rest of the code is what you provided before hand that I did not understand. Bframe Attempts is MY QVD that I want to track the number of records of. The Code I provided was the lower half of your code with my attempted adjustments to track my QVD records. I am just not sure I am placing my QVD in the correct spots so that it records records but does not overwrite the QVD's data.

I appoligize for not explaining better.

Re: Can it be done???

MalcolmCICWF — Thu, 21 Jun 2012 15:53:58 GMT

Here is the full code I have copied from your earlier reply and I modified it to try and incorporate my QVD. I reload and I get a table name, the load date, but no volume, what am I doing wrong?

Re: Can it be done???

erichshiino — Thu, 21 Jun 2012 16:18:39 GMT

You don't need anything up to line 39.

When I used noofRecords = noofRows( x ), the argument x is a resident table (in my case it was transactions)

Since you are using a QVD as your target, you can use qvdnoofRecords as we used in the if statement in line 51

You also need to remove the (qvd) on line 42.

Hope it helps,

Erich

Re: Can it be done???

MalcolmCICWF — Thu, 21 Jun 2012 17:30:29 GMT

I have to be so close, but I cannot get the number of records to generate, the field is just blank. Any ideas?

Re: Can it be done???

erichshiino — Thu, 21 Jun 2012 17:41:29 GMT

I'd say it is very close now

In line 16, change NoOfRows( ... to QvdNoOfRecords ( ....

Then, in line 25, change the if to

if $(QvdnoofRecords) > 0 then

Regards,

Erich

Re: Can it be done???

MalcolmCICWF — Thu, 21 Jun 2012 19:30:31 GMT

I did what you mentioned and that all made sense, but then I got the below error, so I figured I needed to write it like '$(QvdnoofRecords) > 0 then

Now the script finished again, but no records! I know the QVD has records, I even tried others to be sure. This thing is getting frustrating.

This is how I am sitting right now

Re: Can it be done???

CELAMBARASAN — Thu, 21 Jun 2012 19:50:54 GMT

Hi,

You should use LET in the line 16 before QvdnoOfRecords and reloadTime to set a variable value.

Celambarasan

Re: Can it be done???

erichshiino — Fri, 22 Jun 2012 02:33:29 GMT

When you don't use SET or LET and just try to create a variable, LET is the default. So, it should work anyway.

I tried some diferente values, and I found that if the the argument of QVDNoOfRecords () is a qvd that doesn't exist, the variable is not created. So, in the case, I would use the funcion in the if again instead of trying to use the variable.

So, in line 16, let the variable as it is.

In line 25, change the if to call the function:

if QVDnoOfRecords ( ' ... etc ....' ) > 0 then ....

Regards,

Erich

Re: Can it be done???

MalcolmCICWF — Fri, 22 Jun 2012 16:30:40 GMT

It finally worked! it also did not like me calling upon a QVD in another folder, it required me to store it in the same folder as the Qvw itself.