Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Creating a Pivot Table with the new GetHyperCubeTreeData method

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 

clipboard_image_0.png

 

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.

clipboard_image_1.png

 

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...