Send Data from the Qlik Load Script

Qlik Sense allows for interactive analysis of data, but sometimes you just want to send data to users via email. This tutorial describes how you can email a table of data during the load process using Qlik Web Connectors and HTML. It also looks at some of the other things you can do with the free SMTP Connector.

Note that this article was written for Qlik Sense Enterprise Client Managed, and the solution still works on that platform. On Qlik Sense Cloud the syntax is slightly different as you have to use the SMTP connector rather than Qlik Web Connectors. Take a look at this post for details of how to do that.

Why Send Data From The Qlik Load Script

QlikView has a couple of features which are very useful which are not provided with Sense straight out of the box. The first is that the QMC sends email alerts whenever a task fails, and the other is the Alerts functionality. Using the SMTP Connector within Qlik Web Connectors allows you to replicate this functionality and do more besides. This blog post looks at sending data to users from within the load script, I’m looking to write up Task failure notifications in a future blog post. As a reminder of how you can send data from QlikView you may want to see this blog post on Sending Data In QlikView Alerts. The SMTP Connector is one of the Standard (i.e. free) connectors provided with Qlik Web Connectors, for details of these you can see this post on Free Qlik Web Connectors. Note that the SMTP Connector has been renamed from the original name of Notification Connector, which provides a bit more clarity around what it does.

What we are going to look at in this post is pushing a table of aggregated data out to an email address on each refresh of the data.

Solution Approach

Typically in a Sense app you are loading a number of details rows and then building tables and charts which provide aggregated views of the data. Using QlikView Alerts or NPrinting the Qlik engine can be used to build these aggregations for you. As we are going to be sending the data from the load script we need to perform the aggregations ourselves at this point. Both Alerts and NPrinting can also trigger mails based on criteria – whilst this example does not cover this always remember the load script is a full programming language so conditionally calling parts of the code is very simple.

In order to show the aggregated data clearly in an email we are going to be using HTML to build a table, with a touch of CSS to make it look tidy. The SMTP Connector allows you to pass the body of the email in the URL, or to link to a local file with the content. As our table may get quite large we are going to write it to a HTML file on disk and tell the connector to pull that in. The process of creating the file has been covered in this previous post on the Qlik STORE Command, but I will tell you all you need here also.

The SMTP Connector can also mail attachments and link to images (perhaps created on the fly using the Sense API), but I am not covering that in this post.

Before You Start – The Prerequisites

The solution given below assumes that your version of Sense supports the command URL IS. This was brought in in the February 2018 release of Sense. It also refers to the SMTP Connector, which was renamed at some point this year, so a up to date version of QWC is recommended. If you are using QWC without a licence (i.e. only free connectors) then you need to keep within the last couple of versions anyway, so an upgrade is always wise.

Building An App Which Sends Data

Now you are all set here are the steps you need to follow to have email sent to you directly from your Qlik load script. I’ve described connecting to some sample data (from the excellent GapMinder site), but obviously you can plumb in whatever data you like. I’ve uploaded my app to Qlik Community should you want to download it, but you should get a fully working app by following these steps.

Setting Up Libraries

This app requires two libraries to be set up.

Firstly set up a Web library called GenericWeb. This can point to any valid web page, as we will replace the URL later on in code. This is still quite a new feature in Sense, and is well worth knowing how to use to avoid loads of different Web connections.

Next set up a Folder connection called TempData to a temporary store on your Sense server or desktop. I’ve used c:\temp\. You can use a different location, just adapt the code later on. As this location will have a copy of your data in it you will need to ensure it is secure.

Creating Encoding Subroutine

A number of the variables which make up the URL which is passed to Qlik Web Connectors need to be URL Encoded. This means that characters which could be misinterpreted need to be changed to a sequence of characters instead.

Create a sub routine to do this encoding:

sub Encode(vEncodeMe, vEncoded)
let vEncoded = replace(replace(replace(replace(replace(replace(replace(vEncodeMe, ':', '%3a'), '/', '%2f'), '?', '%3f'), '=', '%3d'), '\', '%5c'), '@', '%40'), ' ', '+');
end sub

The first parameter is the value to be encoded, the second parameter is a variable to be populated with the encoded value.

Setting Constants

These three variables store some environmental values. The variable vQwcConnectionName is used when Qlik Web Connectors when it generates Standard Mode code for us, so it is worth using that name. The vConn is just to make our URL shorter later, as we can refer to the variable. Finally vEmailFile defines where our staging file will be written, this must match the folder of the library created earlier.

let vQwcConnectionName = 'lib://GenericWeb';
let vConn = 'https://localhost:5555/data?connectorID=NotificationConnector';
let vEmailFile = 'c:\temp\EMailOutput.html';

The URL of your Qlik Web Connector server may differ, so enter that in here. You may require a machine name or domain reference here, rather than localhost, if your QWC instance is on another machine.

SMTP Settings

Now we define the settings for the SMTP connection. Note that some of these variables use the Encode sub-routine we created, others we don’t need to use it for.

call Encode('notifyme@wherever.com', vMailRecipients);
call Encode('smtp.gmail.com', vSMTP);
let vPassword = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
call Encode('someaddress@gmail.com', vFromEmail);
call Encode('Sending data from Sense Load Script', vSubject);
call Encode(vEmailFile, vEmailContent);
let vUseSSL = 'True';
let vSSLMode = 'Implicit';
let vPort = '465';

The settings above are for a GMail account. You will need to plumb in the correct settings for whichever SMTP server you wish to use. As this process is for sending emails only it is a good idea to set up a new account expressly for this purpose.

The password needs to be encoded before it is entered here. This can be done using the Qlik Web Connectors UI. First go to your QWC page (https://localhost:5555/) and go to the SMTP Connector settings (this is under the Standard connectors). In here you can set up and test your SMTP settings, including entering your password. The code which is generated on successful running will include the encoded version of your password. Extract this from the URL and enter it in the right place, it will appear after Password= and before the next ampersand (&).

Note that whilst the password looks very different it needs to be passed to SMTP as the original string. This means that a two way encoding mechanism is used, so someone who has your encoded password could in theory turn it back to the original one. Hence the advice to set up a new account just for sending.

There is a function in QWC where you can send a plain text password and receive the encoded one back (the Helper connector). This means you can enter the password in plain text in your script. I wouldn’t recommend this approach though.

Note that the Subject is included in these variables. If you want to have a dynamic subject (e.g. with record count) you can move that line to later in your code.

Get Some Data

Here you need to get the source data for whatever table you want to email around. If the application you are creating is purely for sending data you may want to bring in the aggregated data that you wish to send. If you are sending from an app that is also used for analysis you probably want detailed data for that analysis, and the aggregated data can be created in the next step.

This statement uses our generic library and grabs some data.

Population:
CROSSTABLE (Year, Population) LOAD
"Total population" as Country,
[2011.0] as [2011],
[2012.0] as [2012],
[2013.0] as [2013],
[2014.0] as [2014],
[2015.0] as [2015]
FROM [$(vQwcConnectionName)]
(URL IS [https://docs.google.com/spreadsheet/pub?key=phAwcNAVuyj0XOoBL_n5tAQ&output=xlsx], ooxml, embedded labels, table is Data)
;

Aggregate That Data

When you create a table in Qlik Sense you will provide a number of dimensions and then some calculations (Measures). This will then give you one row per distinct combination of dimensions.

We need to create that same table in code. This can be done with a GROUP BY statement, like this.

AnnualData:
LOAD
Year,
num(sum(Population), '#,##0') as [World Population],
num(max(Population), '#,##0') as [Largest Population],
num(avg(Population), '#,##0') as [Country Average]
RESIDENT Population
GROUP BY Year
;

In this instance we are creating one row per Year. Additional dimensions can be added to the load list, each of these also need to be added to the GROUP BY statement, in a comma separated list.

The expressions given in a GROUP BY load are very similar to those used in the expressions in Qlik objects in the front end, but there are some differences. Try the expressions that you would use to see if they work, if not you will need to modify. Probably the most notable difference is that you do not have access to Set Analysis in the load script.

Load Header Information into a Table

We are going to write the contents of a Qlik table to a file to then send as an HTML email. So first we need to put the preamble into the table.

EMailOutput:
LOAD
[<!--EMailOutput-->]
INLINE [
<!--EMailOutput-->
<html>
<head>
<style>
tr:first-child td {font-weight: bold;background-color: #dddddd;}
h3 {Font-family: Arial;Font-size: 12pt;}
td {border-left:1px solid #555555;border-top:1px solid #555555;font-family: Arial;font-size:9pt;text-align:left;padding: 2px 10px 2px 10px;}
table {border-right:1px solid #555555;border-bottom:1px solid #555555;border-collapse:collapse;}
</style>
</head>
<body>
<h3>Population stats for past five years</h3>
<table>
<tr><td>Year</td><td>World Population</td><td>Largest Population</td><td>Country Average</td></tr>
];

Note that the field name will be written to our output file, so it needs to make sense as HTML. This is why I have put a HTML comment there.

A style sheet is given here, which means that the table which is written out with the data in can be much simpler as all styling is handled by the CSS.

Add The Data Table

We have the data we need in a table, we now just need to format it so it displays right in HTML and add it to our table.

CONCATENATE(EMailOutput)
LOAD
'<tr><td>' & Year &
'</td><td>' & replace([World Population], ',', '&#44;') &
'</td><td>' & replace([Largest Population], ',', '&#44;') &
'</td><td>' & replace([Country Average], ',', '&#44;') & '</td></tr>' as [<!--EMailOutput-->]
RESIDENT AnnualData
ORDER BY Year DESC
;

Here we are constructing a number of HTML table cells to properly contain each value in our data. Note that one row of text will be written for each row of the table.

Note that we need to do a replace on the values to URL Encode the commas in the numbers. This is not because the commas will not display correctly in HTML, rather than when the STORE statement is called later it will place double quotes around any values that include commas in them. This will break our HTML. If you don’t use commas as thousand separators then you are good to go without the replaces.

Append On The Footer Information

Just as we started with the header information we now need to concatenate on the footer information.

CONCATENATE(EMailOutput)
LOAD
[<!--EMailOutput-->]
INLINE [
<!--EMailOutput-->
</table>
</body>
</html>
];

You have probably noticed that we are simply building up our code in bite-sized chunks. In the same way we could build in a number of tables and bits of narrative in one email. We are not limited to a single table of data here.

Write The HTML to a Text File

Now we have our table constructed with all of our HTML code we need to write this to a file that the SMTP connector can use. This is simply a case of using the STORE statement that you have probably used for writing QVDs, but here we are saying that we want this written as a text file.

STORE EMailOutput INTO [lib://TempData/EmailOutput.html] (txt);

If your Library was set to point to a web server, perhaps on a UNC path or on the Sense server, then this output can be served up on a web page. This is a great way of outputting data from Sense for a wall board. A very simple Sense extension can be used to make Sense into a web server for this purpose – I will write this up in a future blog post.

For now though we are just using this output in the SMTP Connector.

Send The Email

Finally we just need to send the email. This is done by pulling data from a web page (the same as any other Qlik Web Connector call), the difference is though that this call interacts with a server to send data and not just receive it.

SendEmail:
LOAD
status as SendEmail_status,
result as SendEmail_result,
filesattached as SendEmail_filesattached
FROM [$(vQwcConnectionName)]
(URL IS [$(vConn)&table=SendEmail&SMTPServer=$(vSMTP)&useSSL=$(vUseSSL)&SSLmode=$(vSSLMode)&Port=$(vPort)&Password=$(vPassword)&to=$(vMailRecipients)&subject=$(vSubject)&message=%40file%3d$(vEmailContent)&fromName=Sense&fromEmail=$(vFromEmail)&appID=],
qvx);

Note that we have three fields coming back from the query. These will confirm to us whether the send was successful or not. If you want to bulletproof your code you should use PEEK to look at these values just to check that the process has done what you expect.

You will see in the URL all of the variables that we have set up. Crucially there is the message= parameter, which then is pointed to a file, and the location of the content we have written to disk.

Note that you can generate the bulk of this code using the Qlik Web Connectors UI, but the code here has all the various parts moved out into variables, which makes it easier to configure.

Example Application

If you have followed the instructions above you should now have an application which emails you data on each reload.

In case this hasn’t quite worked out for you, or if you would just like to check your workings, you can download my completed application from Qlik Community. You can find this here:

https://community.qlik.com/t5/Qlik-Sense-Documents/Qlik-Sense-App-Send-Data-From-The-Load-Script/ta-p/1481383

The application also has a sheet which shows the two sets of data (aggregated and raw), the result from the SMTP send and the HTML generated as a table.

If you have any questions regarding this technique please put them in the comments below, thanks!

Community Update!

Héctor Muñoz has kindly shared this post on his blog, and not only that he has taken it to another level. With the code above it is only possible to send tables of data. What Héctor has done is provided the code needed to get data in the load script, parse it, send it to the Image Charts site to get a chart and then include that in the body of the email. This is awesome and opens up some very interesting possibilities. Many thanks Héctor for doing this.

The blog post has unfortunately now been taken down.

We always welcome people commenting on or expanding the things we do in our posts. Please used the comments field below if you have something you would like to share.