You may already know that you can send alerts via email using QlikView when certain conditions are met. These are useful, but it is not obvious how to include data relating to the alert. This post and example app explains how.
Getting The Environment Ready
QlikView Alerts are emails that can be sent from QlikView Desktop or from QlikView Server when a condition is met. The subject, message body and recipients can all be configured with expressions – if required.
Before you can send alerts from QlikView Desktop you first need to configure an SMTP server to send from. This is done by selecting Settings \ User Preferences and then picking the Mail tab. Input your details here and click OK.
The settings of where to send alerts when apps reload in QlikView Server is done in the QMC. The same SMTP settings are used as those that are used to send reload failure notifications, so it is likely these will already be configured in your environment.
Sending a Basic Alert
Alerts are configured in QlikView Desktop under Tools \ Alerts. The dialog for configuring a basic alert looks like this:
To create an alert click the Add button on this dialog. Enter a description for the alert and the values for the Subject, Message and Recipients for this alert. You will notice that these values all have fixed text at present, but there is an ellipses next to each box meaning that expressions can be used (more on that later).
The condition is a simple comparison of a Sum to a fixed value. This is part of what defines when the Alert will fire. In this example there is a Bookmark (containing a selection on the Date field of =Date = today()-1) that is stored as part of the alert – the sum of the Value for the previous day is calculated and then compared to a fixed value. If the yesterday value is less than 10,000 our alert will fire.
It’s not just the condition being met that means the alert will fire. We can chose when this is checked. Generally you will want On Post Reload checked, and optionally On Open. Obviously, only post reload works on Server.
The Delay sets how many days (or parts of days, if you put in a decimal) the condition has to be in the given state before the alert fires. More often than not this will be set to zero.
The Trigger Level is more generally useful. When an alert is fired a cache of the text is kept, and if Message Changes is selected the alert will only send again if the content of the message is different to the last time the alert was set. You can also select to send every time (e.g. each reload) or only once until the condition is cleared and then met again.
I would recommend not using the Show Popup tick box. This shows a dialog box to the user based on a condition. If you want to show something on screen on a condition a text box with a show hide condition is often the better be. If you use Variables correctly you can have the same variable as the condition on both the alert and the text box.
The final options are setting what environments the alerts will fire in. The tick boxes for Interactive and Batch basically refer to QlikView Desktop and the QMC reloads.
That concludes what you need to do for a basic alert, and that is where many people leave their alerting – but you can do more.
Using AGGR To Catch Exceptions
The example above looks at all data that is loaded and compares that against a single value. Often though it is the case that you want to check a condition against each value in a dimension or a combination of dimensions. This is one of the places where the AGGR function is essential.
If you have not used AGGR before; what the function does is builds a virtual Straight Table in memory with one or more dimensions. Expressions can then be done inside that virtual table and these expressions are done for each row. This virtual table of dimensions and an expression can then either be used as a Calculated Dimension or (as in this case) have a further aggregation on top of it.
In the example I have created for these alerts I wanted to do a calculation of Sales vs. a Target for each of a number of dates. If on any date the target was not met then this date should be counted. This feeds into the alert Condition and also the Subject.
The code to get a count of Dates where the Sales are less that target is:
sum(aggr(if(sum(Sales) < sum(Target),1,0), Date))
The If part of the statement returns 1 for days missing target, and zero for those that meet or exceed. The AGGR part gives the ability to work it out for each Date. Multiple dimensions can be provided here if required. The Sum statement then tots up all the ones.
If you have every written an expression where you tried to put one aggregation around another and it broke – you probably needed to put an AGGR in there as well.
This count of days can now be used for both the Condition and in text.
Building a Single Text String for the Alert
Native alerts in QlikView can only send text strings. More complex emails (including chart images, embedded tables and attachments) can be sent by using QlikView NPrinting, but some sending of data can be done using just the standard tool-set.
What is required is to build a single text string that contains all the information we need. Once again we need to employ AGGR as we need to look at each dimension value separately. The expression this time however returns text, rather than an integer. Because of this we need to use CONCAT rather than Sum to bring these together. CONCAT joins all the values together, with a delimiter you specify yourself (or the default is comma separated).
So for a list of all dates where target was not met we can simply do:
=concat(aggr(if(sum(Sales) < sum(Target), Only(Date), null()), Date), ' | ')
Here we get a list of each date, separated by a pipe symbol.
We can then expand this out to what we want to see in the email we send by appending our count to the front, and expanding what happens inside the ‘true’ part of the If statement.
='Sales target have been missed on ' &
sum(aggr(if(sum(Sales) < sum(Target),1,0), Date)) &
' days in the last 14
' &
concat(aggr(if(sum(Sales) < sum(Target),
Only(Date) & ':' &
right(' ' & num(sum(Sales) - sum(Target), '#,##0'), 10) &
' ' & num((sum(Sales) - sum(Target)) / sum(Target), '#,##0.0%')
,null()), Date), '
', Date * -1)
Note that in order to put carriage returns in our output they are embedded in single quotes in the expression. As well as the dates we have some text as dividers and the Delta and Variance to target. The Right statement is in there to make the second column right aligned and always the same length.
It’s always a good idea to test out your message text in a text box on the screen before putting it into an alert. You could also put the text (or constituent parts of it) into variables.
Putting It All Together
Now we have worked out how to create a simple alert, found out how to count issues across a dimension and how to build a string of data, we can put these things together to build our alert. The alert screen now needs to look like this:
Note that the description is set in the same way as our basic alert. The condition however now has our AGGR expression in it. Similarly the expression above for building the full text string is now appearing in the message. The subject also has the count we have derived embedded in it.
The email received by recipients (set in the variable) will now include the dates and values for each of the exceptions.
Downloadable Example
A QVW demonstrating these principles and with code you can copy, paste and adapt for your own alerts is available on Qlik Community here:
If you download the example and find it useful, please take the time to rate it on the Community. This makes it easier for others to find these resources.
A list of other Quick Intelligence example and tutorial files can be found here.
This should give you all you need to build more complex alerts. Good luck!
It is a nice document Steve thanks for sharing. Regarding N – Printing can we send a particular chart in a set of available charts as a image or mail attachment to the customer using N printing.
Hi Kiran. Yes. You pick objects from QlikView (e.g. charts) and drop them into a template. When the schedule runs those objects are placed into the output accordingly. The generated report can then be sent as an attachment or in the body of the report.
Hi,
Why did you use Only(date) instead of date in if condition in aggr?
You are correct that it will have no functional difference. Generally the use of the only function does not. What it does do is make it clear you are expecting only one value in that field at that time, due to the dimensions of selections. It’s kind of a way of commenting the code by using code.
Hi Steve,
Can you suggest some technique by which I can draw the lines in the above tabular format?
something like this:(the header labels preferably in bold) . I want to send a tabular data through mail alerts, like you have illustrated in the example.
———————————————-
Column1 | Column 2 | Column 3 |
———————————————-
Data | Data | Data |
———————————————-
Data | Data | Data |
———————————————-
I’m not sure if you can send HTML in an email when sending from an alert. If you can it would just be a case of adding the right tags to the output – it may be that this just shows the HTML in the body of the mail though.
You could write the HTML to a file, using the technique I describe in this blog post: Write To CSV with Store.
You can then embed this in the body of a mail using the free Qlik Web Connector, as described here: Free Qlik Web Connectors.
The issue with this approach though is that you have to calculate the figures and raise the alert in the load script, rather than the front end. It has the advantage it can be done with Qlik Sense though, which is a distinct bonus.
Hope that helps.
Thanks Steve.
The methods you suggested look really interesting.
I was wondering if something could be done using the ‘-‘ (hyphen symbol) and | (pipe symbol) and using repeat function of qlikview.
Thanks.
Hi how to do this same thing in Qlik Sense ?
Hi Vikas,
There is nothing built in to Qlik Sense to do this. The nearest to a solution (without building this in an extension) that I have found is to use the free Qlik Web Notification Connector. I’ve talked about these free connectors in this post. This approach will only work in the load script though, so you will need to use GROUP BY etc. in the load statement to discern whether you will need an alert to be sent.
Hi, Steve!
What if a mail-server use TLS encoding only?
We have used a tool called STunnel in the past to map to TLS servers, or you could see my post on using QWS for sending emails. You should be able to make it work one way or another.