I’m a newcomer to Qlik, and am working on implementing Qlik Sense for a hospital. One area I was considering today was how to better communicate to users when there might be something wrong or they need to be aware of with the data they are looking at.

Our data model has quality checks as part of the data load scripts, and was reporting these to various exception flags. Maybe in QlikView this might work because the experience can be more tightly controlled; you could set up a Data Quality tab, add in all of the various exception flags from around the data model and then filter each of the exceptions. But I don’t want to rely on having to do that, let alone if other staff are building any of their own apps.

So I searched for a better solution, for about an hour. My first thought was ‘could I setup an Exception table’, which could have different numbers and exception reasons linked. To be effective though one would somehow have to then have an Exception link table and insert a row dynamically linking back to any rows in any tables which had data quality issues. I’d still like to figure this one out, but this is not a job for today.

Next I considered using bitmask status codes.. you might have come across these before if you’ve ever had to change settings in the registry. That way I could at least use one column in each table as an Exception column, and be able to indicate a number of different exceptions all in the same field. But because of the coding you can still separate it out later (i.e. from a data quality page) to find out what exceptions each row had. Also not a job for today, and I don’t think our Qlik partners would appreciate trying to debug that!

Finally I was going to try appending a written exception into a single column, but again I don’t have enough Qlikfu to figure that one out.

So I reverted to prioritising the exceptions in an if statement, and just setting ‘Exception’ to be whatever reason is matched first.

Unfortunately none of the above solve my original question of how do we warn users about this datum. (I mention all these because I’d love to know what solutions people have come up with, maybe someone out there sees this and things ‘oh I can explain how to do that in a forum post!’)

Next part of the challenge – how do indicate this to the user. My current text exception column is taking up precious screen real-estate and ‘Ex Procedure Has Exception = 1’ doesn’t mean much anyway.

So I thought I would try out using Emojis in Qlik. Couldn’t find anything online except someone having issues with Emojis in source data freezing Qlik

Went to http://getemoji.com/ looking for a Warning⚠️ symbol (which looks nice and Yellow and sufficiently warning in Firefox) and pasted this into a string in Qlik Sense. To my surprise it pasted into the editor pane and showed up among the Console font text (I thought at least would have to do some special Unicode /x01513/ string or something to make it work). Thankfully, it survived a Load Data and made it through to the data Table I was working on:

In this first screenshot I’ve set the Procedure name up to append a warning Emoji if the procedure has exceptions. I’ll come back to the ‘ApplyMap’ bit later, but either of the lines below would achieve the same result.

Procedure:
Load *,
     // Append warning emoji to procedure name if has exceptions
     [Procedure Name] & If([Procedure Exceptions],ApplyMap('Emoji','warning'),'') As [Procedure]
;

Unfortunately we can’t set the hover text on the emoji to show what the issue is, so we’ll still need a Warnings tab of some sorts.

(Emoji’s are actually just rendered as text, not images, so there isn’t even an image there to attach hover text to).

Next up the stock order table, wanted to show a quick visual way whether the order was closed or not.

Sure we could use conditional formatting, but that relies on the user configuring it for each table they are building (unless there is some Qlikfu to ‘tag’ a column for conditional formatting, hint hint). Using this method if they add the Closed flag they can see it is closed.

In this instance I set it up as a Dual to retain the 0 or 1 of the underlying data for counting purposes. i.e. running a count on the screenshot below would give you 6.

If(closed, Dual('✔️',1), Dual('✖️',0)) AS [Order Closed],

Annd you can even sort by or filter on our little picture friends:

They also work in KPIs (to show up nice and big), export to Excel and PDF OK (but don’t look as pretty).

Please note they will look bland on Internet Explorer but should show up nicely in Firefox or Chrome, so you need to test from the devices of your users.

I hope this has given you some ideas on how you might be able to improve communication when you’re stuck using a Table for some reason!

Here is a Mapping to get you started, copy and paste into Data Load script, then select which Emoji you want using ApplyMap.

Of course you can just copy and paste from the Unicode or Getemoji website as well, but this way gives some more control if you want to change things up centrally down the track.

Emoji:
Mapping LOAD * Inline [
  Keyword,Emoji
  warning,⚠️
  idea,?
  config,⚒
  lock,?
  unlock,?
  attach,?
  calendar,?
  heart,?
  noentry,⛔️
  recycle,♻️
  tick,✔️
  cross,✖️
  alarm,?
  speech,?  
  megaphone,?
  marker,⛳️
  apple,?
  moon,?
  sun,☀️
  weather,⛅️
  cloudy,☁️
];

Would love to know of other ways you’re doing exception reporting or interesting uses of Qlik Sense!