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:
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!