Triggering events with Qlik, IFTTT and Webhooks

Something I often say is that a Qlik dashboard is only worthwhile if it causes someone to take an action. But what if that action could be taken automatically? IFTTT (If This Then That) is a platform that allows different services to talk to each other and trigger events. In this post I look at how a Qlik load script can start an action. Specifically, it defines how you can play We Are The Champions on the office stereo when a new customer is won. Obviously.

If This, Then That

Data only becomes information if it becomes useful and actionable. Until that point any pushing of bits and bytes from data-source to screen is a purely academic exercise. In order to take action we need to get the information that is required to the right place. We have already looked at email and wall-boards to get information out beyond Sense or QlikView. In this post I look at opening up the massive toy box that is IFTTT.

IFTTT (If This Then That) is an online service that allows you to connect to many different IoT services which provide both triggers and actions. It then provides a simple interface for making one of those triggers cause one of the actions. For instance, you may want to turn your central heating on when your mobile phone gets within five miles of home after work, or make your lights flash on and off when your Domino’s Pizza is approaching. This all becomes even more powerful when you can connect your data as well, perhaps you want to post to your company’s Slack channel when stock is low, or Tweet an alert to followers when the price of one of your products drops.

In this post we are going to do something far more useful. We are going to replace the ‘new customer’ bell in the office by making the office Sonos system blast out We Are The Champions by Queen each time a new customer is spotted in our data. You may, of course, choose other songs, triggers or actions. The possibilities are limitless.

Setting Up IFTTT

In order to make this happen we first need an IFTTT account. Signing up is quick, simple and free. Head to https://ifttt.com/ and you can either login to, or create, your account.

If you are using your email address to sign in you will need to set yourself a password, or if you are using Google or Facebook you simply need to sign into the corresponding account.

When you first come in to IFTTT it will prompt you with a Applet that you may want to run. You can ignore this one and X out of the welcome screen. You will then be given the enticing invitation to Start Connecting Your World.

Once in you can explore the massive number of Applets, curated by IoT manufacturers, service providers and the IFTTT community. If you look around you are sure to find something that will improve your digital life. We are going to create our own Applet from scratch though, so we can do something bespoke.

Building Your First IFTTT Applet

To start creating on the IFTTT platform click on your profile icon at the top right of the page. This will currently just be a plain head-shot icon, you can obviously customise this if you wish. From the drop down menu select create. You will then be prompted with what IFTTT is all about:

Click on the This part of the statement.

This will then show you logos for all of the services which are on the IFTTT platform which have trigger actions. For what we are creating we want a Webhook. This creates a unique URL that you can call, with parameters, to start an action. These Webhooks can be called from anything that can pull a web page, including Qlik, so are perfect for our purpose.

Search for Webhooks in the box at the top, or you will be scrolling for a while. Click on the icon and you will be prompted to connect to the Service.

Click the Connect button to continue.

You will then be prompted to Choose Your Trigger. Webhooks are very simple so there is only one trigger option here, other Services can have many more. Click the Receive A Web Request button to continue.

You will now be prompted to enter an event name for your trigger. You can have many Webhooks for different tasks, this name is what identifies this particular Applet and it will form part of the URL to be called. For my Champions applet I’m going with the event name of champs.

Once you have entered a name click the Create Trigger button.

The Webhooks icon appears in the If This Then That text, and it is now time to click the That link to specify what will happen. I’m going to search for Sonos here, you may have a different action you want to kick off, there are plenty of devices and services to choose from.

If you are setting up Sonos for the first time with IFTTT you will be prompted to Connect and clicking the button will bring up a dialog where you can sign into your Sonos account and link this to IFTTT. Other services work in a similar way.

Once connected you will be presented with all of the available actions for your service. For Sonos there is a Play Favourite option. Before clicking this you will need to ensure that the song you wish to play is a favourite on your Sonos system (it seems this is presently not available in the Sonos app, but it can be done from the i button on the Desktop player).

Once the button to play a favourite has been clicked you will be prompted to Complete Action Fields. Here you can select your favourite and which room it should play in. Again, other services will have different properties but all work in much the same way.

When the properties have been set you can click Create Action to move to the next page. You will be shown the name that IFTTT has decided to give your applet (a literal description of what it does). You may want to over-type this with something more meaningful, perhaps “Play We are The Champions when we land a new client”. You also have the option to be notified when the task runs – you probably want to turn this off. Click the big Finish button and you have your Applet.

Obtaining The Webhooks URL

You now have your Applet, the only problem is that you don’t yet know how to trigger it.

On the page that shows your completed Applet, click the Webhooks icon, this will take you to your Webhooks page. If this is your first applet you will see just that one here. From here click the Documentation button. Other Webhooks applets will show here as you add them.

This then shows a helpful page, giving more information about Webhooks, and importantly also your unique Maker Key. In the first URL here type the trigger name that you specified earlier on (it’s champs if you followed my example). Once that is typed you can copy the whole URL to your clipboard and paste it into a new tab in a browser.

With a bit of luck and a following wind you should get a friendly message, saying Congratulations, you have fired the champs event and the office Sonos system should spring into life (you may want to warn colleagues first).

Keep this URL on your clipboard, as you will be needing it later, when we move to Qlik. Note on the Webhooks documentation page there is the ability to add three parameters to the URL. We are not going to use these in this example, but if you wanted to put some values from your data into your IFTTT applet this will allow you to do so.

Triggering the Action From a Qlik Load Script

The beauty of Webhooks are that they can be called from a myriad of different places. Want to trigger a task each time someone hits a page on your website? Webhooks make it possible.

As this is a Qlik blog though we are going to look at calling it from a Qlik load script. For the purposes of this post I am going to use Qlik Sense Business in the Cloud, but you can use Sense Enterprise, Sense Desktop or QlikView if you wish.

You may want to embed this into an existing app, but I’m going to describe here creating an app purely for the purpose of starting the song.

To create a script that triggers your action, first get to your Sense Hub and create a new app. It’s always good to give you apps a sensible name and enter some meta-data about the app. Click to open the app (the Open button in Qlik Sense Business) and then the button to go into the Script Editor. The data manager makes simple loads easier, but if you are embedding logic the Script Editor is always the way to go.

Once in the Load Script Editor click to Create a new Connection.

For some reason, the Web connector is not available in Qlik Sense Business at present, hopefully this will appear soon. You can use the web connector if you are in another version of Sense, or using QlikView (this has fewer parameters to specify and works just fine).

If web connection is not available for you select the REST Connector.

The URL that you copied back in the step where you found your API key needs to be copied into the URL box.

As the result from this URL is plain text (as we saw earlier) rather than JSON you need to un-tick the Check response type during ‘Test Connection’ check box. Things will fail otherwise (this is not required with the Web connector). All other defaults are fine.

Give your connection a sensible name (I’m going for Champions) and click Test Connection. All being well you should get an okay message, and your IFTTT action should trigger. You can now click Save to create your connection. The URL will be called again at this point.

You can now click the Select Data (middle) button to insert the script for calling the URL into the script.

Tick the tick-box next to CSV_source, all other defaults are fine. Then click the Insert Script button and this will insert the following script:

LIB CONNECT TO 'Champions';

RestConnectorMasterTable:
SQL SELECT 
	"col_1"
FROM CSV (header off, delimiter ",", quote """") "CSV_source";

[CSV_source]:
LOAD	[col_1]
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

The loading into a temporary table and then again into a secondary table isn’t required for what we want, so you can delete the CSV_Source table and the DROP statement from your script.

It’s always a good idea to rename tables to something sensible. This is done just by editing the script ahead of the select statement. Change RestConnectorMasterTable to IFTTT.

LIB CONNECT TO 'Champions';

IFTTT:
SQL SELECT 
	"col_1"
FROM CSV (header off, delimiter ",", quote """") "CSV_source";

Click the Load Data button to test this.

If you are already skilled in writing Qlik load script, or you are calling your Webhook some other way, this is all you need to know. You can call your Webhook URL from wherever you wish and it will fire your applet.

If you want to see how you can identify a new customer in your data using the Qlik load script, please read on.

Identifying New Customers in the Qlik Load Script

In order to spot new customers we are going to be using QVDs. Hopefully you already have a robust ETL strategy in place, using QVDs to persist staging data. If not it will be a good idea to read up on using QVDs before proceeding. If you are using QVDs and have separate QVD generator apps then it is in the QVD generator stage that the calling of your IFTTT alert should sit.

You will need to have a library created in order to store your QVDs. This is created in much the same way as the Champions REST connector above. In Qlik Sense Business you have a choice of Amazon S3, Azure, Dropbox or Google Drive to store your QVDs. On Enterprise and Desktop you can go for a Folder (most likely) or Dropbox.

For my example I am using a Dropbox connection, with the name Dropbox. Your library name might be different, you will need to change it in the code below.

The example assumes that you have a table loaded called MainData which includes a field called Customer Name. You will need to change both the table and field names to match your own data.

I would suggest you add a new Section in your load script, called New Customers for this code. If this is in a QVD generator type app it will need to sit between the data load and where you drop the table after writing the QVD. In a front-end app the new section can sit at the end of the load script.

Before we start, we need to initialise a variable which we will use later on, in a TRACE statement:

let vIFTTT = '';

Now we need to get a distinct list of customers from our main data table. This is done using a RESIDENT load, as follows:

CustomerNames:
LOAD DISTINCT
    [Customer Name]
RESIDENT MainData;

You will only be able to know you have new customers if you already have a QVD of customer names. This will be created at the end of the first run, but we need to skip the next bit if we don’t have the QVD yet. Do this with the following IF statement, after the code above:

if alt(FileSize('lib://Dropbox/ExistingCustomers.qvd'), 0) > 0 then

end if

Note that there is no FileExists function in Qlik load scripts, so we need to use a combination of FileSize and Alt (which returns 0 if a null (missing) value comes back from the exists function).

If the file exists we can load the previous set of customer names from the QVD, renaming the field in the process, and then get only the customer names from our customer list where they not in the previous list. The renaming ensures we are keeping in mind which customers are which. Insert the following code inside the IF statement.

    ExistingCust:
    LOAD
        [Customer Name] as [Existing Customer]
    FROM [lib://Dropbox/ExistingCustomers.qvd] (qvd);

    NewCust:
    LOAD
        [Customer Name] as [New Customer]
    RESIDENT CustomerNames
    WHERE NOT EXISTS ([Existing Customer], [Customer Name]);

    DROP TABLE ExistingCust;

We now have two tables in play, our complete list of customers and another table with only customers who were not present at the last run. Following the DROP TABLE statement we then want to check if there are rows in the NewCust table and if so trigger our alert. We can also write a file with the new customers in at that point.

Following the DROP TABLE statement add the following:

    if alt(NoOfRows('NewCust'), 0) > 0 then
        LIB CONNECT TO 'Champions';

        IFTTT:
        SQL SELECT 
            "col_1"
        FROM CSV (header off, delimiter ",", quote """") "CSV_source";

        let vIFTTT = replace(peek('col_1', -1, 'IFTTT'), chr(39), '');
        TRACE $(vIFTTT);
        DROP TABLE IFTTT;

        STORE NewCust INTO [lib://Dropbox/NewCustomers.txt] (txt);
    end if

Note that some extra lines of code are added from our call of the event previously. This reads the result from the Webhook and displays it in the load script log, and then cleans up by removing the table. Note the replace of the single quote from the output, this single quote breaks the Trace if left in the string.

The list of new customers is also written away to a text-file here. This will contain the last added customers and be dated at the point they were first seen in the data.

Outside of both IF blocks we now need to write away and clean up the customer list table, this is so we have a table to check against the next time the script is run.

STORE CustomerNames INTO [lib://Dropbox/ExistingCustomers.qvd] (qvd);

DROP TABLE CustomerNames;

That should now do what we require, and tidy up after itself.

The completed code, with comments, is shown at the end of this article.

I’d be very interested to hear what you achieve using these techniques. Please do this in the comments below. There are many many more possibilities of using IFTTT in this way and also for embedding logic in the load script for parsing and alerting based on your data.

This code can be expanded further in a number of ways. If you have multiple Webhooks you may want to have a single REST connection and change the URL that you call. This can be done using the WITH CONNECTION statement, changing the URL as required. Webhooks can receive three different parameters, which can then inject ‘ingredients’ into actions. If you want to trigger an event which reports the number of New, Existing and Total customers then this is again made possible using WITH CONNECTION in your script.

This is, perhaps, a frivolous example of how to use this tech, but I hope it gives you some ideas of what you can do with Qlik and IFTTT.

Completed Example Code

Your New Customer load script section should look something like this, when it is completed:

// Initialise a variable for the Trace statement later
let vIFTTT = '';

// Get a distinct list of current customer names
CustomerNames:
LOAD DISTINCT
    [Customer Name]
RESIDENT MainData;

// Check whether we have a previous list of customer names
if alt(FileSize('lib://Dropbox/ExistingCustomers.qvd'), 0) > 0 then
    
    // Load the names of customers we already know
    ExistingCust:
    LOAD
        [Customer Name] as [Existing Customer]
    FROM [lib://Dropbox/ExistingCustomers.qvd] (qvd);

    // Find the customers who don't exist in the previous list
    NewCust:
    LOAD
        [Customer Name] as [New Customer]
    RESIDENT CustomerNames
    WHERE NOT EXISTS ([Existing Customer], [Customer Name]);

    DROP TABLE ExistingCust;

    // If there are some new customers raise the alert
    if alt(NoOfRows('NewCust'), 0) > 0 then

        // Call the IFTTT Webhook
        LIB CONNECT TO 'Champions';

        IFTTT:
        SQL SELECT 
            "col_1"
        FROM CSV (header off, delimiter ",", quote """") "CSV_source";

        // Report the result from the Webhook and tidy up
        let vIFTTT = replace(peek('col_1', -1, 'IFTTT'), chr(39), '');
        TRACE $(vIFTTT);
        DROP TABLE IFTTT;

        // Write a copy of who the new customers are
        STORE NewCust INTO [lib://Dropbox/NewCustomers.txt] (txt);
    end if
end if

// Persist the customer list, so we can identify new ones next time
STORE CustomerNames INTO [lib://Dropbox/ExistingCustomers.qvd] (qvd);

DROP TABLE CustomerNames;