QLIK Sense: If Then Conditional Logic

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.

2016-12-28_09-54-14.jpg

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

2016-12-28_09-42-49

2016-12-28_10-19-45

This will give you the following Bar Chart

2016-12-28_09-43-38

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.

2016-12-28_10-10-15.jpg

Let’s go to the Data load editor

2016-12-28_09-25-05

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

2016-12-28_09-25-24.jpg

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

2016-12-28_09-25-39

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.

2016-12-28_12-17-24

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.

2016-12-28_12-18-21.jpg

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

2016-12-28_12-25-20.jpg

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

2016-12-28_09-44-17

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’

2016-12-28_12-17-45

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

2016-12-28_09-45-31

 

 

 

 

Qlik Sense: A first Impression

So I am trying out Qlik Sense as a possible BI option for work. I have some limited experience (through grad school) working with another one of their products Qlik View. I will say right from the get go, Qlik Sense is much more user friendly.

Opening it up, I get the following splash screen

qlikSense.jpg

Qlik then asks if I would like to Create a New App.qlikSense1.jpg

I click Create A New APP and I get a window to name it.

qlikSense2.jpg

It says New app created, and asks if I want to open my app

qlikSense3.jpg

Now I am brought to a page to add my data. I have two options, one a more GUI driven method, and the other that will allow me to create my data model using Qlik’s script (very similar to SQL).

I am going to go with the GUI method for now.

qlikSense4.jpg

I choose Excel files

qlikSense5

Next I select a file QlikTEst.xlsx that I put together for this test drive.

qlikSense6.jpg

My file appears in preview mode. Notice I am able to select both sheets to load and unselect columns I don’t want.

qlikSens7.jpg

I click Prepare Data at the bottom and I am brought to this screen. Notice the red triangle warning on the bottom of the screen. It says I have possible associations.

qlikSens8.jpg

I click the warning and I now have a recommendation for an association to make between sheets. My Equipmentkey column matches up(53%) on both sheets.

qlikSens9

Just double click the Recommendations box and your pages are now joined

qlikSens11

Now hit Load data

qlikSens12.jpg

Qlik now brings you to a work sheet.

qlikSens13.jpg

I want to create a bar chart, so I drag one in from the menu column.

qlikSens14.jpg

Now I pick my Month as my dimension

qlikSens15.jpg

And I choose WorkOrderNo as my measure

qlikSens16.jpg

Now I choose an Aggregate of Count()

qlikSens17.jpg

And I have a bar chart.

qlikSens18.jpg

Now, let’s add a filter – drag it in from the menu column

qlikSens20

Select FacilityCode as my dimension

qlikSens21.jpg

I can adjust the filter size by clicking on these points

qlikSens22.jpg

Now hit done at the top of the screen

qlikSens23.jpg

And I now have a live interactive bar chart.

qlikSens24.jpg

My Thoughts on Qlik Sense

Qlik Sense is insanely easy to get up an running. You really don’t need to know much more than how to drag and drop. However, if you are like me and enjoy getting under the hood, Qlik does allow you custom code your data models and formulas.

My only complaint, and this could be because I am new and don’t know a work around, is having to hit Done to leave the edit screen before I can test the interactivity of my charts.