Qlik-Simple-Email-Example

Example of how to leverage Qlik Web Connectors to send email alerts from within your Qlik (Sense or View) load scripts.

Overview

This example uses Qlik Web Connectors (Notification) to send notifications/alerts to users. Example use cases could be to alert end users of changes in their data or to send messages to DevOps, Administrators, etc... who need to be alerted of load script/data issues.

Basic Flow

Load script executes (1) and sends a URL request to the Qlik Web Connector (QWC) Notification Connector (2) which sends an email through your SMTP server, which ultimately gets delivered to your user (3).

Installation & Setup

1. Install, Configure, and License Qlik Sense or QlikView 1 OR 2.
2. For Qlik Sense, ensure that you disable Standards Mode for the Engine by following the steps in the Qlik Documentation 3.
3. Install, Configure, and License Qlik Web Connectors (QWC)4.
4. Test sending an email from QWC
   1. From the Qlik Web Connectors home page, select the "Standards" Connectors tab
   2. Click on the "Notiication Connector"
   3. Select "SendEmail" and select "Parameters" button
   4. Complete the fields and select "Save Inputs & Run Table" to validate it works
      1. NOTE: Some servers may require you to add port :587 to the URL.
   5. This is a required step in order to make sure the credentials are cached on the QWC server and can be used from within your script.
   6. Select the Qlik Sense or QlikView tab in QWC and copy the Script.
5. Open any of your existing Qlik Apps that you want to send an email from in Qlik Sense (or QlikView).
6. Edit the load script and paste the Script you copied above.
7. Reload the App and check your email.
8. After testing is completed, you can implement this in your production applications.

Example Application

This Example Simple QWC Email Example has the load script below as an example of what this could look like in your final application. In this example, an email with the application name and id can be sent to your administrator on a successful (or failure) loading of the application. This example application has the following four sections:

1. Main - Standard Main section with specific variables defined.
2. Script Utilities - Loading a URL Encoding table from w3schools to encode strings and defines SendEmail() function
3. Data Load - Example data load, introduces errors, and calls SendEmail() function.

SECTION: Main

Standard Main section with specific variables defined.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SECTION: Script Utilities

Loading the URL Encoding table from w3schools to encode strings.

//URL Encoding Mapping Load - Helps to encode the URL to avoid errors
URL_Encoding_Reference:
MAPPING LOAD
    Replace(Character,'space',' ')	 as Character,
	Text("From UTF-8")				         as URL_Encoding
FROM [https://www.w3schools.com/tags/ref_urlencode.asp]
(html, utf8, embedded labels, table is @1);


//SendEmail Subroutine
Sub SendEmail(inTo, inFrom, inSubject, inBody)

  // STEP 1: Change the QWC Server path
  SET vSEQWCBaseURL = 'http://localhost:5555/data';

  // STEP 2: Verify/Modify Notification connect string here
  SET vSEQWCNotification 	= '$(vSEQWCBaseURL)?connectorID=NotificationConnector&table=SendEmail';

  // STEP 3: Change the SMTP server name, after testing on QWC server and caching the credetials
  SET vSEQWCNotification = '$(vSEQWCNotification)&SMTPServer=smtp.gmail.com%3a587&useSSL=True';


  // Encode the fields so that they get to QWC correctly, this requires the URL_Encoding_Reference ApplyMap in Script Utilities
  LET vSESubject	= MapSubString('URL_Encoding_Reference',inSubject);
  LET vSEBody 	 	= MapSubString('URL_Encoding_Reference',inBody);
  LET vSETo 	 	= MapSubString('URL_Encoding_Reference',inTo);
  LET vSEFrom 		= MapSubString('URL_Encoding_Reference',inFrom);

  SET vSEQWCNotification = '$(vSEQWCNotification)&html=True&to=$(vSETo)&from=$(vSEFrom)&subject=$(vSESubject)&message=$(vSEBody)&delayInSeconds=15&appID=';

  LET vSEMessage = 'QWC Send Email Connection: $(vSEQWCNotification)';
  TRACE $(vSEMessage);

  NotificationConnector_SendEmail:
  LOAD status as SendEmail_status,result as SendEmail_result,filesattached as SendEmail_filesattached
  FROM [$(vSEQWCNotification)] (qvx);

  //Clean up subroutine table
  DROP TABLE NotificationConnector_SendEmail;

  //Clean up subroutine variables
  LET vSEQWCBaseURL = null();LET vSEQWCNotification = null();LET vSESubject = null();LET vSEBody = null();LET vSETo = null();LET vSEFrom = null();LET vSEMessage = null();

End Sub

SECTION: Data Load

Example data load, and calls SendEmai() function.

Characters:
Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;

ASCII:
Load
 if(RecNo()>=65 and RecNo()<=90,RecNo()-64) as Num,
 Chr(RecNo()) as AsciiAlpha,
 RecNo() as AsciiNum
autogenerate 255
 Where (RecNo()>=32 and RecNo()<=126) or RecNo()>=160 ;

Transactions:
Load
 TransLineID,
 TransID,
 mod(TransID,26)+1 as Num,
 Pick(Ceil(3*Rand1),'A','B','C') as Dim1,
 Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,
 Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,
 Round(1000*Rand()*Rand()*Rand1) as Expression1,
 Round(  10*Rand()*Rand()*Rand1) as Expression2,
 Round(Rand()*Rand1,0.00001) as Expression3;
Load
 Rand() as Rand1,
 IterNo() as TransLineID,
 RecNo() as TransID
Autogenerate 1000
 While Rand()<=0.5 or IterNo()=1;

//Comment or Uncomment these three rows to trigger a Logic Error in this example.
 ErrorMode = 0;
bad stuff here;
 ErrorMode =1;

 Comment Field Dim1 With "This is a field comment";

// STEP 4: Change the following variables/logic to affect the recipient, sender, subject, and message
LET vTo			= '[email protected]';
LET vFrom 		= '[email protected]';

if $(ScriptErrorCount)>0 then
  LET vSubject 	= 'Application: ' & DocumentTitle() & ' had $(ScriptErrorCount) errors during reload';
  LET vBody		= 'Application: ' & DocumentTitle() & ' (ID: ' & DocumentName() & ') please review load script log messages.';
else
  LET vSubject 	= 'Application: ' & DocumentTitle() & ' successfully loaded';
  LET vBody		= 'Application: ' & DocumentTitle() & ' (ID: ' & DocumentName() & ') successfully loaded.';
end if  

Call SendEmail('$(vTo)', '$(vFrom)', '$(vSubject)', '$(vBody)');

Next Steps:

Now that you have this working and have started sending emails from your load scripts, you can see my Qlik Proactive Email Notifications github project to provide a system to send proactive data alerts to users.

References:

1. Qlik Sense Install Documentation: http://help.qlik.com/en-US/sense/Subsystems/Installation/Content/InstallationLicensing/Server-Deployment-Introduction.htm
2. QlikView Install Documentation: http://help.qlik.com/en-US/qlikview/Subsystems/Server/Content/QlikView%20Server/QVSRM_Install_Upgrade.htm
3. Disable Standards Mode: http://help.qlik.com/en-US/sense/Subsystems/Hub/Content/LoadData/disable-standard-mode.htm
4. QWC Install Documentation: http://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/2.1/Installation/Installing-the-web-connectors.htm