Summary: In this post I suggest a case for formatting measures as JSON to allow for easy consumption in an Engine-API webapp.
In a recent customer webapp project with my partners at Websy, I found that using JSON for data in Qlik hypercubes was a useful technique.
The app displays an overview of metrics collected throughout the organization, along with contextual clues and drill & focus capabilities.
The data consists of a varying number of metrics or measures Measures may be added or removed by appearing in the data. Contextual data such as labels and color coding is provided in the data.
I can’t share the actual customer app. Here is a simplified data example to demonstrate with.
The real data contains additional complexities, but this sample will suffice. Using this data, a section of the dashboard output would appear like this:
A row is generated for each Category and columns are generated for each measure.
Current month values, (“Flag_Current=1”), are displayed alongside available history (“Flag_Current=0”) for the Date selected in the “Compare to” dropdown.
The CompareDirection is used to to control whether an increase in this measure should be scored as positive or negative.
There is some asymmetry in the data. Attributes such as “Format”, are available only in the Current data but are required in the History data as well. History is available for some measures, but not others. So what’s the best way to handle the asymmetries? Not uncommon in Qlik, we can use set analysis and expression functions such as “Aggr()” or “TOTAL” to propagate the attributes.
As the application requirements and data model grew, the expressions and sets became more complex and difficult to manage and validate. This was true whether we provided the data as additional columns or qAttributes.
On the javascript side the row and column data are processed into an object structure to prepare for visualization. The type of propagation required here — assigning current.Format to history.Format — is trivial in javascript.
Since we are going to transform this data into objects, I thought why not deliver the data as objects already?
The text representation of a javascript object is JSON. Here’s what the Qlik table will look like with JSON output.
I created a Qlik script variable to help generate the JSON.
Set AsJson = '"$1": ' & if(IsNum($1) and text($1) = num($1), $1, '"' & $1 & '"');
The measure for the “Current Values” column generates an array of objects. The Measure expression is:
'[' &
concat({1<Flag_Current={1}>}
'{'
& $(AsJson(Date))
& ', ' & $(AsJson(Label))
& ', ' & $(AsJson(Value))
& ', ' & $(AsJson(Color))
& ', ' & $(AsJson(Format))
& ', ' & $(AsJson(SortOrder))
& ', ' & $(AsJson(CompareDirection))
& '}' , ', ', SortOrder)
& ']'
The definition for the “History Values” is similar with only the set expression and the fieldnames changing. The set expression to get this “collection” of data is specified only once and we don’t have to be concerned with repeating the set in multiple definitions.
Javascript to consume the data might look like this. Note the JSON.parse() to consume the JSON.
const columns = [];
layout.qHyperCube.qDimensionInfo.forEach(info =>
columns.push(info.qFallbackTitle));
layout.qHyperCube.qMeasureInfo.forEach(info =>
columns.push(info.qFallbackTitle));
const senseData = layout.qHyperCube.qDataPages[0].qMatrix;
senseData.forEach(row => {
let currentValues =
JSON.parse(row[columns.indexOf('Current Values')].qText);
let historyValues =
JSON.parse(row[columns.indexOf('History Values')].qText);
let category = row[columns.indexOf('Category')].qText;
renderRow(category, currentValues, historyValues);
});
I find several advantages in the JSON approach.
- Fewer expressions and sets to maintain.
- A closer match between the HyperCube and the object model used in the app, making for easier understanding and validation.
- Less transformation code on the javascript side.
I’m not suggesting that JSON is always the best way to deliver data. We found it useful in this particular case and I wanted to share the experience so you could keep it in your tool bag as well.
-Rob
Want to learn more on this topic or advanced Qlik development? Join us at the Masters Summit for Qlik in Amsterdam (28-30 Oct) or Washington DC (6-8 Nov). For traditional Qlik Devs, we’ll be teaching advanced skills in data modeling and expressions in the QS/QV Track. Experienced JS developers will want to attend the Qlik API Track for a deep dive into creating webapps and mashups.
