Getting all data cells from app.createCube()

[Yianni_Ververis]

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) {

[#2]

Which version of qlik sense are you using?

The getHyperCubeData method is not valid in the context of the object returned from app.createCube.

I also tried getting the model from app.getObject, that too does not have the getHyperCubeData method in the context.

Do you have a working sample anywhere?

[stevenkoppens]

Great post!

Thanks

[Yianni_Ververis]

Hello Prateek,

we are on 3.1 but the model is only available by a resolved promise not a callback

This is the entire function in my service..

https://gist.github.com/yianni-ververis/bf749fe306c88198de2b6ceb043712e3

[eliasmazur]

I'm building a customized search box and I'm trying to use your function.  My problem is that I need to quickly provide a search facility to over 20,000 items.  I tried using getData but it's very slow and unacceptable for what I need to do.

When I use your function, all is fine until the Promise.all().  It returns zero items in the data array.  I tried using $q from an injector and also using:  var Promise = qv.getService('$q').

Any help is appreciated.

Thanks

Elias

[Anonymous]

Hi Yianni,

Where do I apply getAllResults.js in this api?

GitHub - miclae76/trellis-chart: Qlik Sense Trellis Chart Extension

Thanks,

André Silva

[Anonymous]

Great Post.

With the help of this post, we are trying to use Jquery Promise and not angular, we do get the number of pages and total columns, but we are facing error on Promise.all.

Mashup js code which we are using:

error: TypeError: Promise.all is not a function

////////////////////

var prefix = window.location.pathname.substr( 0, window.location.pathname.toLowerCase().lastIndexOf( "/extensions" ) + 1 );

var config = {

host: window.location.hostname,

prefix: prefix,

port: window.location.port,

isSecure: window.location.protocol === "https:"

};

require.config( {

baseUrl: ( config.isSecure ? "https://" : "http://" ) + config.host + (config.port ? ":" + config.port : "") + config.prefix + "resources"

} );

require( ["js/qlik"], function ( qlik ) {

debugger;

qlik.setOnError( function ( error ) {

$( '#popupText' ).append( error.message + "<br>" );

$( '#popup' ).fadeIn( 1000 );

} );

$( "#closePopup" ).click( function () {

$( '#popup' ).hide();

} );

//callbacks -- inserted here --

debugger;

//app3

var app = qlik.openApp('Data Scalability Test App.qvf', config);

  $(".qvobject").each(function() {

        var qvid = $(this).data("qvid");

        app.getObject(this, qvid);

    });

app.createCube({

        qDimensions: [{

            qDef: {

                qFieldDefs: ["Customer ID"]

            }

        }],

        qMeasures: [{

            qDef: {

qDef: "Avg(\"Sales\")",

                qLabel: ""

            }

        }]

    }).then(

function(model) {

debugger;

var deferred = jQuery.Deferred(),

promises = [],

qTotalData = [];

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

debugger;

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) { 

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

}else{

var Promise = $; 

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){

//Promise.all.bind(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); 

}); 

}

});

return deferred.promise;

});

});

///////////////////

We do get the promises as array (one for each page), but I am not sure about the Promise.all function, any help is highly appreciated.

Also, suggest, if this approach is not valid and needs changes.

Thanks in advance,

[andyjalexlive]

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

[Yianni_Ververis]

Yeah this is weird. Try with different dimension

[andyjalexlive]

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

[Yianni_Ververis]

Change

data.qDataPages[0].qMatrix

to

data[0].qMatrix