Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
Yianni_Ververis
Employee
Employee

As I was working on one of my latest mashups, I had a task, to get all cells with numerous sets of dimensions and measures, with a HyperCube out of Qlik Sense. The worst part is that one of my sets, had a total of 3+ million cells!

We know that the initial HyperCube returns only up to ~10k cells, So the question is how do we paginate to get the rest or even get them all in one call!

We also know that, most of the Capability API methods are using promises, something very helpful, especially if we make a lot of calls to Qlik Sense and expect to return the results only when all of the promises have been resolved. This is where promises “.all()” come into play.

Even though ES6 has promises build in, not all browsers support it yet, so I will use Angular's $q to handle those promises.

First we have to make the initial call and get the maximum rows and columns. We start by creating the cube with the dimensions

app.createCube({

     qDimensions : qDimensions,

     qMeasures : qMeasures

}).then(function(model) {

Once we have the model ready, we make the initial call to get the first set of results and get the HyperCube info like total columns and total rows

model.getHyperCubeData('/qHyperCubeDef', [{qTop: 0, qWidth: 20, qLeft: 0, qHeight: 500}]).then(function(data) {

Once we have that we can determine how many total pages do we have

var columns = model.layout.qHyperCube.qSize.qcx;

var totalheight = model.layout.qHyperCube.qSize.qcy;

var pageheight = Math.floor(10000 / columns);

var numberOfPages = Math.ceil(totalheight / pageheight);

Now, if we got all of the results, we should end the method and return those results

if (numberOfPages==1) {

     deferred.resolve(data.qDataPages[0].qMatrix);

}

Otherwise, we should get the data from all pages and then return the results! Promises in full glory!!!

var Promise = $q;

var promises = Array.apply(null, Array(numberOfPages)).map(function(data, index) {

var page = {

          qTop: (pageheight * index) + index,

          qLeft: 0,

          qWidth: columns,

          qHeight: pageheight,

          index: index

     };                                                                                          

     return model.getHyperCubeData('/qHyperCubeDef', [page]);

}, this);                                                               

Promise.all(promises).then(function(data) {

     for (var j=0; j<data.length; j++) {

          for (var k=0; k<data.qDataPages[0].qMatrix.length; k++) {                                                      

               qTotalData.push(data.qDataPages[0].qMatrix)

          }

     }

     deferred.resolve(qTotalData);

});

This is how we get all of the results for a given set of Dimensions and Measures.

If you want to paginate just create a function that passes the page+1*500 to the qInitialDataFetch like

app.createCube({

     qDimensions : qDimensions,

     qMeasures : qMeasures,

     qInitialDataFetch : [{

          qTop : (pageIndex)*500,

          qLeft : 0,

          qHeight : 500,

          qWidth : 20

     }]

}, function(reply) {

Or

model.getHyperCubeData('/qHyperCubeDef', [{qTop: (pageIndex)*500, qWidth: 20, qLeft: 0, qHeight: 500 }]).then(function(data) {

13 Comments
andyjalexlive
Contributor III
Contributor III

Hi

thanks for this Yianni

I have got stuck on this part though deferred.resolve(data.qDataPages[0].qMatrix);

The error message states Cannot read property '0' of undefined

The other weird thing is that the value of data.qHyperCube is undefined. The total height is returned by model.layout.qHyperCube.qSize.qcy so it's like the hypercube is only holding the meta data but no actual data

Any help is much appreciated.

Andy

0 Likes
2,111 Views
Yianni_Ververis
Employee
Employee

Yeah this is weird. Try with different dimension

0 Likes
2,111 Views
andyjalexlive
Contributor III
Contributor III

Hi Yianni

So now the value of console.log(data) is:

  1. [{…}]
    1. 0:
      1. qArea:{qLeft: 0, qTop: 0, qWidth: 2, qHeight: 500}
      2. qMatrix:(500) [Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), …]
      3. qTails:[{…}]
      4. __proto__:Object
    2. length:1
    3. __proto__:Array(0)

and the error that comes from deferred.resolve(data.qDataPages[0].qMatrix);

is Cannot read property '0' of undefined

my Code:

model.getHyperCubeData('/qHyperCubeDef', [{qTop: 0, qWidth: 20, qLeft: 0, qHeight: 500}]).then(function(data) {

var columns = model.layout.qHyperCube.qSize.qcx;

var totalheight = model.layout.qHyperCube.qSize.qcy;

var pageheight = Math.floor(10000 / columns);

var numberOfPages = Math.ceil(totalheight / pageheight);

if (numberOfPages==1) {

console.log(data);

deferred.resolve(data.qDataPages[0].qMatrix);

} else {

If you can help me understand what is going on that would be great.

Have a good weekend


Andy

0 Likes
2,111 Views
Yianni_Ververis
Employee
Employee

Change


data.qDataPages[0].qMatrix


to


data[0].qMatrix

0 Likes
2,111 Views
andyjalexlive
Contributor III
Contributor III

Hi Yianni,

Thanks that worked. I now see the data returned form the hypercube. The issue i now have is it doesn't return information about dimensions and measures such as hc.qDimensionInfo.qFallbackTitle

Any idea how i return this hypercube meta data as well?

Best


Andy

0 Likes
2,111 Views
diegobormann
Partner
Partner

There is an error in the above script.

I found that on larger data-sets (with multiple pages) a couple of data-points where missing. This is why:

  1. var page = { 
  2.           qTop: (pageheight * index) + index
  3.           qLeft: 0
  4.           qWidth: columns, 
  5.           qHeight: pageheight, 
  6.           index: index 
  7.      };             

The + index should not be there!, on every new page it is going to skip 1 or more rows!


Regards,

Diego


0 Likes
2,111 Views