One place Qlik really shines in my opinion is in their data modeling. While Qlik Sense brought user friendliness to the mix by allowing automatic data model creation, it still allows almost unlimited customization to those looking to peek under the hood.

In today’s example, I am going show you how to utilize a simple IF THEN statement in the data model to make building your visualizations much easier.

You can download the practice data set here: qlikalarms

This data set is made up data modeled off of cardiac patient alarms. Let’s look at our data:

As you can see, we have 5 columns. Count is just a distinct number for each row, day = day of the week, alarm trim down1 = the alarm condition, Alarm Class = severity of alarm – Red is the most severe with INOP being the least, timeday = time of day the event occurred.

The Task

The task at hand here is simple enough. We want to analyze alarms on the weekend versus weekday.

Loading the data in Qlik is easy enough. If you have any questions on how to do it, refer to my earlier tutorial on building a Dashboard: QLIK: Build a Dashboard Part 1

Once your data is loaded:

Go to the App Overview. Select your new sheet, hit Edit and grab a Bar Chart from the left utility bar. We are going to set the Dimension to Alarm Class and the Measure to Count of alarm trim down1

This will give you the following Bar Chart

Now grab a Filter Pane and drag it onto the sheet. Set its dimension to day.

Now by selecting days from the filter pane you can effectively compare weekdays to weekends. However, it involves a lot of unnecessary clicking for the end user. Let’s try a better method.

Let’s go to the Data load editor

click on the Auto-generated section in the left pane.

Next click on the Unlock box in the upper right corner. You will be met with a warning window. Just click Okay.

Let’s take a look at the Load script. This script was auto-generated by Qlik when you uploaded the Excel file. Note that it looks similar to an SQL script. We are going to LOAD the columns listed below FROM the Excel workbook.

What we are going to do next is add a new line to the loading script. This line will be an IF THEN statement.

The syntax is as a follows: if ( conditional statement, THEN, ELSE) as [NAME FOR NEW COLUMN]

In our example I am stating if day is equal to ‘Saturday’ or ‘Sunday’ then 1 else 0 and I am naming this new column Weekend

*** note the , at the end of [timeday]. Make sure you add that there. Qlik will throw an error if the correct syntax is not used.

Now select Load data. If successful, go back to your App Overview > edit sheet. If not successful, check your syntax!!

Let’s replace the day filter pane with a Weekend filter pane

Now you can compare weekdays to weekends with just a single click. 1 for weekends and 0 for weekdays.

This still is not ideal. The goal of a good BI solution is usability. The end user should be able to dig into their data without having to spend too much time trying to decipher what built.

Let’s correct this. Go back to data load editor. Let’s change our IF THEN statement to read if day = Saturday or Sunday, then ‘Weekend’, else ‘Weekday’

Click Load data and go back to your sheet. Notice the filter pane now shows Weekday and Weekend as your options.