A Qlik visit to the dentist

by | May 12, 2016

One of life’s necessary, but expected to be unpleasant experiences, is a visit to the dentist. For an SQL developer, pivoting or unpivoting data can be a similar experience. In my case, I needed to unpivot and then include the requirement for a dynamic number of values stored in a column and I’m thinking tooth extraction! I don’t say it often but Qlik, I’m impressed.
I was given a table of disk performance data to graph. There would be multiple servers with multiple drives. This would be time series data where all the measurements for a server reading would be represented by a single row of data. The numbers I wanted were stored in a couple columns in array format [C;,D:,….] and [50,3,….] So this is what I had:

pivot_before

Source: OptimalBI

This is what I needed to do anything useful with the data:
unpivot_after

Source: OptimalBI

My first thought was maybe I needed to do some transformation of the database table before I loaded it into Qlik. Thankfully, I decided I should have a look at what Qlik could do for me. My searching led me to these articles:

  • This post to clean up array [ ] in the text using the PurgeChar function
  • This post to unpivot the data using SubField function

Combining these guides, my problem was solved with a few lines of code.

LOAD
  Measurement_date_time,
  Server_Name,
  subfield(purgechar( Disk_Name,'[]'),',', IterNo()) as Disk_Name,
  subfield(purgechar( Read_Pct,'[]'),',', IterNo()) as Read_Pct
 WHILE iterno() <= SubStringCount(purgechar( Disk_Name,'[]'),',')+1 ;
SQL SELECT
  Measurement_date_time,
  Server_Name,
  Disk_Name,
  Read_Pct
FROM Vault.dbo.Performance;

Databases may be catching up in regards to doing this sort of thing but Qlik has had it for years.
All the code, all the fun – Ben
Ben writes blogs about the technical side of BI the code, all the code and not much other than the code.
You can read all of Ben’s blog here.
Don’t forget, we can train your team in the art of agile business intelligence at any time!

0 Comments
Submit a Comment

Your email address will not be published. Required fields are marked *