Sending data from the Qlik load script

Qlik Sense enables interactive data analysis, but sometimes you just want to send data to users by email. This tutorial describes how to email a table of data during the load process using Qlik Web Connectors and HTML, and touches on some of the other things you can do with the SMTP connector.

Why send data from the Qlik load script

QlikView has a couple of very useful features that Qlik Sense does not provide directly: the QMC sends email alerts whenever a task fails, and the Alerts functionality. Using the SMTP connector within Qlik Web Connectors lets you replicate that functionality and, on top of that, do more.

The SMTP connector is one of the standard (that is, free) connectors that come with Qlik Web Connectors. What we will look at here is pushing a table of aggregated data to an email address on every data refresh.

The approach to the solution

Normally, in a Sense app you load a series of detail rows and then build tables and charts that provide aggregated views. Since we are going to send the data from the load script, we have to perform the aggregations ourselves at this point.

To display the aggregated data in an email, we will use HTML to build a table, with a little CSS to make it look tidy. The SMTP connector lets you pass the body of the email in the URL or link it to a local file. As our table can grow, we will write it to an HTML file on disk and tell the connector to pick it up from there.

Before you start: prerequisites

The solution assumes that your version of Sense supports the URL IS statement (introduced in the February 2018 release) and references the SMTP connector from Qlik Web Connectors, so it is best to use an up-to-date version of QWC.

Setting up the libraries

This app requires two libraries:

A web connection called GenericWeb. It can point to any valid web page, since we will replace the URL later in the code.
A folder connection called TempData to a temporary store on your Sense server or desktop (for example c:\temp). Since a copy of your data will be left there, make sure it is a secure location.

Encoding subroutine

Some of the variables that make up the URL passed to Qlik Web Connectors need to be URL-encoded: characters that could be misinterpreted are swapped for an escape sequence.

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 encode; the second is a variable that gets filled with the encoded value.

Setting constants

These three variables store some environment values. vQwcConnectionName is the name of the web connection, vConn shortens our URL later on and vEmailFile defines where the file will be written (it must match the library folder above).

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

Your Qlik Web Connector URL may differ; if your QWC instance is on another machine, use the machine name or domain instead of localhost.

SMTP configuration

Now we define the settings for the SMTP connection. Some variables use the Encode subroutine; others do not need it.

call Encode('notifyme@email.com', vMailRecipients);
call Encode('smtp.gmail.com', vSMTP);
let vPassword = 'XXXXXXXXXXXXXXXXXXXXXXXX';
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; adapt the configuration to your SMTP server. As this process only serves to send emails, it is a good idea to create a dedicated account for the purpose.

The password must be encoded before you enter it here. You can do this from the Qlik Web Connectors interface (https://localhost:5555/ → SMTP connector, under Standard Connectors): when you test the connection successfully, the generated code includes the encoded version of the password. Bear in mind that this is a two-way encoding, so anyone who has the encoded password could, in theory, reverse it: hence the advice to use a dedicated account.

Getting some data

Here you bring in the source data for the table you want to send. This example uses GapMinder data through our generic library:

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);

Aggregating that data

When you create a table in Qlik Sense, you give it some dimensions and some measures, and you get one row for each combination of dimensions. We need to create that same table in the script with a GROUP BY statement:

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 case we create one row per year. Each additional dimension must be added both to the load list and to the GROUP BY statement. Bear in mind that in the script you do not have access to Set Analysis, so test your expressions beforehand.

Loading the HTML table header

We are going to write the contents of a Qlik table to a file to send it as an HTML email. First, the preamble:

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 #555; border-top:1px solid #555; font-family: Arial; font-size:9pt; text-align:left; padding: 2px 10px;}
table {border-right:1px solid #555; border-bottom:1px solid #555; 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>
];

The field name will be written to the output file, which is why we have set it as an HTML comment. The stylesheet lets the data table be much simpler.

Adding the data table

Now we format each row as HTML and concatenate it:

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;

Notice the replace: we encode the commas in the numbers because, when STORE is called later, double quotes are placed around any values that include commas, and that would break our HTML. If you do not use the comma as a thousands separator, you can drop the replacements.

Just as we started with the header, we now concatenate the footer:

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

We are building the HTML piece by piece; in the same way we could assemble several tables and text fragments. We are not limited to a single table.

Writing the HTML to a file

We write the built table to a file the SMTP connector can use. It is a simple STORE, like the one you probably use to write a QVD, but specifying that it should be text:

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

If your library points to a web server, this output can be published on a page: a great way to get data out of Sense and onto a wall-mounted dashboard.

Sending the email

Finally, we send the email. This is done by pulling data from a web page (like any other Qlik Web Connector call), the difference being that this call interacts with a server to send data, 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);

The three fields the query returns confirm whether the send was successful. To make it bulletproof, use PEEK on those values and verify the result. In the URL you will see all the variables we have configured; the key one is the message= parameter, which points to the file with the content we wrote to disk.

You can generate most of this code from the Qlik Web Connectors interface, but here we have parameterised it with variables to make it easier to set up and maintain.

At Digital Fox Data we solve this kind of automation —and everything else in the load script— with our Qlik consulting, and we teach your team to master it in our Qlik courses. Do you have a Qlik challenge? Let’s talk.