Generating test data for QlikView and Qlik Sense

Real data and test data – both are needed

One of the great things about both QlikView (QV) and Qlik Sense is their integrations with other systems. Given the native connectivity to any ODBC source, web pages in general, Salesforce, REST APIs, BigQuery etc – combined with the dozens of connectors provided by tools like QVSource – I have yet to find a single system or data source we could not pull data from.

The great thing about this is that it makes it easy to quickly get some real data and then start building your application.

That said, sometimes you need a limited data set to start with, or just some conceptual data to try out an idea on. Enter online test data generators, which can be used to generate test data for

Continue reading “Generating test data for QlikView and Qlik Sense”

Posting to Slack from Qlik Sense load scripts – web service style (part 2)

As the first version of the Sense-to-Slack integration required you to lower the Sense security level, it is probably not a good option for enterprise Sense users, for example.

Instead, let’s set up a small node.js web service, which exposes an API that we can call just like we can retrieve data from any web page in the load script.

There are a couple of pre-requisites:

  1. Node.js must be installed on the machine where Sense is running (or some other machine in your network – for sake of simplicity I assume it’s on the same machine). You also need to have the Node libraries Restify and Node-Slack installed.
  2. You also need Qlik’s REST connector, at least if you are to avoid some messy URL character encodings. More on that later.

Setting up the Node.js server

Create a directory where you will store your Node apps. C:\node or something similar – you decide what’s suitable on your system. Then, get the code for the node server from GitHub. The code is pretty basic (make sure to use your own Slack web hook URL):

var restify = require('restify');
var Slack = require('node-slack');

var slackWebhookURL = '<fill in your web hook URL from Slack>';
var slack = new Slack(slackWebhookURL);

function respondSlack(req, res, next) {
// console.log(req.params);

slack.send({
 text: req.params.msg,
 channel: req.params.channel,
 username: req.params.from_user,
 icon_emoji: req.params.emoji
 });

res.send(req.params);
 next();
}

var server = restify.createServer({
 name: 'SlackProxy'
});

server.use(restify.queryParser()); // Enable parsing of http parameters
server.get('/slack', respondSlack);

server.listen(8080, function() {
 console.log('%s listening at %s', server.name, server.url);
});

Run the server:

C:\node\slack_proxy>node slack_proxy.js
SlackProxy listening at http://[::]:8080

Log Slack entries from Sense load script, using the REST connector

Connection to Slack proxy

Now, create a new Sense app to try out the notification. In that app, set up a new data connection using the REST connector:

With the new connection in place, use the rest connector to connect to the proxy, and let the REST connector generate the code for you.

Finally, add the “WITH CONNECTION”… statement to send in the actual data that should be passed on to Slack. A good thing here is that the REST connector handles encoding of special characters like space, # etc – no need to do that manually. Very nice!

The resulting load script looks something like this (your data connection names will differ, of course):

LIB CONNECT TO 'Slack';

RestConnectorMasterTable:
SQL SELECT 
 "channel",
 "from_user",
 "msg",
 "emoji"
FROM JSON (wrap on) "root"
WITH CONNECTION (
 QUERY "channel" "#general",
 QUERY "from_user" "SenseBot",
 QUERY "msg" "Posted from Sense",
 QUERY "emoji" ":smile:"
 );

Slack:
LOAD 
 [channel] AS [channel],
 [from_user] AS [from_user],
 [msg] AS [msg],
 [emoji] AS [emoji]
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

Reload the app, and voila – in Slack we get this:

Slack message created by Sense load script

Mission accomplished.

Log Slack entries from Sense load script, using the FROM command

If you don’t want to use the REST connector, or don’t have it installed, there is another way of achieving the same result as above.

Your Sense load script will instead look something like this:

Slack:
LOAD
 *
FROM [http://localhost:8080/slack?channel=%23general&from_user=SenseBot2&msg=Another%20message%20from%20Sense&emoji=:smile:]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Notice how we have to convert space, # and other “special” characters to their hex equivalents? You could write a small function that does this for you (don’t think Sense has any built in function for URL escaping – or?), but with the REST connector being so easy to use – that should be the preferred option for most people.

In this case we get a Slack message like this:

Slack message using FROM command

 

 

Posting to Slack from Qlik Sense load scripts, part 1

When thinking about it, it’s kind of strange that Slack has been so enormously successful.

Sure, it’s a great instant messaging tool, but it’s not a new concept – IM tools have been around for decades, and IRC, Google chat and others rose to be very dominant and present in everyone’s minds.

Still, Slack does bring something new with the ease of setup, the slick user interface, really good clients on both web, Windows, OSX, IOS and Android. And more than anything else, they provide a very extensive set of integrations (also mentioned in earlier post on great OSX tools), which makes it a breeze to have other systems send messages, status notifications etc to your Slack channel. Or vice versa – messages in Slack can be sent to those other systems. As of this writing the list of integrations is more than 85 entries long, including services like Dropbox, IFTTT, Twitter, various email integrations, Jira etc.

Continue reading “Posting to Slack from Qlik Sense load scripts, part 1”

Qlik Sense servers need time server connectivity to stay in sync

QlikLogoProbably pretty obvious when thinking about it in retrospect, but this one hit us the other week.

After setting up some Qlik Sense servers without any real issues (rather the opposite – it is incredibly quick and easy to deploy an Enterprise scale Sense system!), one day one of the rim nodes fell off the cluster and showed up as red in the QMC.

The Windows server itself was fine, we could RDP into it without any problems whatsoever. But after trying to restart the Sense services on that server, none of them came back up, except for the Repository Database service. Nothing really helpful in the logs either. Rebooting the entire server didn’t help. That rim node was dead in the water.

Continue reading “Qlik Sense servers need time server connectivity to stay in sync”

Qlik Sense and QlikView code snippets available as GitHub Gists

I finally got around to start using GitHub Gists a bit more systematically for storing useful bits of code. Going forward I will post useful, reusable pieces of Qlik Sense and QlikView code there. My own experience tells me there is tons of time to be saved by reusing existing code rather than writing it from scratch each time..

All gists available here.

IMHO GitHub could improve the gists concept by adding tags to them, that way it would be way easier to find relevant gists. Or I’ll move to some other tool if/once I find it… Any suggestions on good ones? Leave a note in the comments!

Does Qlik Sense make sense? Not always…

Qlik Sense is pretty cool. Ok, it’s early days and they are still on version 1.x, but still – lots of potential there.

And the name is kind of neat too – Qlik’s marketing people must have had a long hard think before settling on “Sense” for their next gen product… There are just so many puns and word games that keep popping up.

Sense did however give us a good laugh the other day. Trying to open a Sense app that was developed some time ago, we got this error message. Now that’s pretty funny..

Continue reading “Does Qlik Sense make sense? Not always…”

Using Slack to monitor Qlikview and Qlik Sense

Slack has only been around since August 2013, but I would definitively say it’s one of the better Slack integrationsteam communication services out there. The web client is great, and the OSX and IOS clients are truly awesome. It integrates with tons of other services, including Dropbox, GitHub, IFTTT, Jira, Google Drive, RSS, Nagios, Yo, Twitter and Pingdom are just some of the services it supports (as of today they seem to support ca 75 integrations). There are also generic connectors for incoming and outgoing webhooks, especially the incoming webhook feature will be interesting from a systems monitoring perspective – it will allows us to post messages to Slack by just calling a certain URL.

In this post we will look how we can use this to both monitor the various QlikView services, as well as monitor the transfer of files (e.g. data files used by QV) to a QV server, and a as a generic way of sending notifications from QlikView Management Console (QMC).

Most of the concepts below also apply to Qlik Sense, of course.

Continue reading “Using Slack to monitor Qlikview and Qlik Sense”

Qlik Sense keyboard shortcuts

Qlik Sense is very nice in most ways, the development environment however still falls short when compared to the one found in QlikView. Having a proper Windows client (which QlikView has) just gives more flexibility than using a web based editor and development environment.

Don’t get me wrong – Sense gets the job done – just that for people whose muscle memory is tuned to the QlikView development environment, Sense feels somewhat limiting and slow. That said, after spending a fair bit of time both porting existing QlikView apps to Sense, as well as developing new Sense apps from scratch, the development environment becomes less and less of an issue. I’d say that Qlik still has way to go though. But hey – we are looking at version 1 – I am sure there are plenty of good things to come in future versions.

Anyway – if you spend significant parts of your days in an application, you want to be as effective as possible in your work. QlikView has quite a few powerful keyboard shortcuts, Qlik Sense has somewhat fewer, but still useful. These are listed in this online Qlik Sense help page.

Seems that the help page is not complete though – some shortcuts are not listed there.
Let’s compile a list of them here, please feel free to add additional ones in the comments!

ShortcutAction
Ctrl-KComment (using //) the lines currently selected in the code editor.