Creating a tool in QlikView to send emails if SQL source data is not successfully updated

[#1]

Hi,

I am trying to create a tool in QlikView which will automatically send emails to specified users if the update of the source data has been unsuccessful.

I have considered two approaches for this:

1.       During the load script, after the data itself is loaded, I create two variables, vToday, vUpdate (the date of last update from the source data). I could then at the end of the load script write an if statement to call the send email code which I have set up as a subroutine if the values in vToday and vUpdate are the same. I have made an assumption here that QlikView will be able to update the values of each variable during the load script and not once this has completed.

2.       Create the variables using settings>variable overview rather than in the load script and then try to use an action (or something similar) to call the send email code as a sub routine based on the value held in that text box.

I was not sure that option 2 was really an option so I have chosen the first option. Open to any better suggestions though, I am very new to QlikView but have experience of developing in VBA.

I have managed to connect to the data source and have the code to send the email. My issue is that when I try to set the value of the vUpdate variable during the load process the variable retains a value of nothing, despite the code running without any error message. I have set the variable in the code on a tab which is run after the data that is used to set the variable has been loaded. However, if I set these using settings>variable overview this work fine and I can see the resultant dates in the text boxes fine after the load script has run. I thought that the issue may be that the vUpdate variable doesn’t recognise what cdate is and that I needed to use resident and the table name to make this explicit but when I add that code I get an error:

  ‘Field not found - <cdate>

LOAD cdate

Resident TABLE

File attached but with OLEDB script missing due to sensitivity, data still exists

Anyone have any ideas on this?

Thank you

[Anonymous]

Ashley,

The script creates field cdate in a separate table, not in TABLE.  If the table name is not specified explicitly, QlikView uses the source name as the table label, in this case it would be TABLE.  But this name is in use already, so it created TABLE-1.  See the data model.

TABLE:
SQL SELECT *
FROM "ICCE_MI".admin.EventLog;

// there is no label here

LOAD MAX(CreateDate) AS cdate
Resident TABLE;

To fix, assign a label yourself, for example

CDATE:
LOAD MAX(CreateDate) AS cdate
Resident TABLE;

And, you variable will be:
LET vUpdate=peek('cdate',0,'CDATE');     // there is no need for num() because max() converts to numeric too

After this you can drop the table:

DROP TABLE CDATE;

Regards,
Michael

[Gysbert_Wassenaar]

Have you looked at the Alerts feature? You can probably use an alert to send the emails.

[Anonymous]

Gysbert is right about the Alerts.  Besides you can set up your Publisher to send emails if reload fails.

(In my reply, I focused not on the emails, but rather on the problem in the script, which is not directly related.)

[#5]

Hi

I am looking at the alerts feature now, i think this will remove the need for me to call a subroutine to send the email but i do not believe it will fully resolve my issue.

I dont think i explained very well in my original post but i have pulled into QlikView data from one of the admin tables in an underlying SQL Data Mart. I don't need to check if the reload in QlikView has taken place but that the admin table in the underlying SQL Data Mart holds an entry to identify that the SQL Data Mart itself was successfully updated.

I think i will still need to use the two variables but after i have these working i can then use the alert to check the condition and send the emails.

I will take on board the issues in the script and let you know how i get on.

Thank you for the guidance