Pivot Table Loading

[Jennell_McIntire]

With the release of Qlik Sense 3.1 this week came the Pivot Table Loading feature.  This feature provides the ability to “un-pivot” data that is stored in a pivot table format.  I am excited about this new feature because I often have data that I need to “un-pivot” in Qlik Sense.  I must admit that in the past I occasionally used QlikView to generate the script to load this type of data using the CrossTable wizard but now with the Pivot Table Loading feature, I can do it all in Qlik Sense.

Let’s take a look at an example.  Assume I have a data set that looks like the image below where the first column is country and I have data values for various years.

When I load this into Qlik Sense, the data is loaded just like the Excel file with a field named Country, 2013, 2014 and 2015 (see image below).  What I would prefer is to have a country field, a year field and then a field that stores the data values.  This is where the Pivot Table Loading comes into play.

In Qlik Sense, I can drag my Excel file into Qlik Sense to load it.  Once the file is loaded I can open the Data Manager and click on the edit icon for the table.

Below in the Data Manager window, there is now the Unpivot option.

When Unpivot is clicked, you are prompted to select the fields that you want to transpose into rows.  Since I want to transpose the 2013, 2014 and 2015 fields into rows, I will select those three columns and select the Apply unpivoting button.  At the bottom of the screen, I am provided a preview of the data and I can see that my years are all in one field now.

Once the unpivoting is applied, I can rename the attribute and data fields and load the data.

Now if I preview the table I loaded, it looks like this:

The 2013, 2014 and 2015 fields have been transposed into rows and I now have a Year field that I can use in a filter pane or in my visualizations.  My script was auto-generated and you can see the use of the CrossTable prefix to load the Excel file.

The Pivot Table Loading feature is a valuable new feature in Qlik Sense 3.1 that will make it easier to load pivot table formatted data.  Check out Michael Tarallo’s video titled Qlik Sense 3.1 - Using Unpivot (video) to see the Pivot Table Loading feature in action.

Thanks,

Jennell