Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All these years when I needed a nested data object to create something like a pivot table, I had to get multiple HyperCubes and then refactor them to have one big object with all of the data so we can display them in a webpage.
Few weeks ago I came across the new GetHyperCubeTreeData method (note that it has a stability index of Experimental). This basically retrieves data in a nested tree with qNodes. Each qNode represents a Dimension with an array of all the values and the qValues is an array of all of the Measures values.
In this example I am connecting to the general Helpdesk app and I will try to create a simple Pivot Table.
The first level of data will come from 'Status' and the seconds level from 'Case Owner Group'. The measure will be '=Count([Case Count])'.
First, let build the TreeData prop
const qProp = {
qInfo: { qType: 'data' },
qTreeDataDef: {
qDimensions: [
{
qDef: {
qFieldDefs: ['Status'],
},
qSortCriterias: [{ qSortByAscii: -1 }],
qValueExprs: [
{ qDef: { qDef: '=Count([Case Count])' } },
],
qShowAll: false,
qTotalLabel: 'Totals',
},
{
qDef: { qFieldDefs: ['Case Owner Group'] },
qSortCriterias: [{ qSortByAscii: -1 }],
qValueExprs: [
{ qDef: { qDef: '=Count([Case Count])' } },
],
qNullSuppression: true,
qShowAll: false,
qTotalLabel: 'Totals',
},
],
qInterColumnSortOrder: [0, 1],
qOpenFullyExpanded: true,
qTitle: 'Pivot Table',
},
};
Then , get the tree data by using the GetHyperCubeTreeData method
const qObject = await qDoc.createSessionObject(qProp);
const qData = await qObject.getHyperCubeTreeData('/qTreeDataDef');
The results should be like
As you see above we have each dimension values nested under qNodes.
Now that we have all the data in one object, we can create the pivot table.
Here is the live example
https://qdt-apps.qlik.com/qdt-components/react/#/pivot-table-engine
More reading:
https://help.qlik.com/en-US/sense-developer/June2019/APIs/EngineAPI/services-GenericObject-GetHyperC...