Qlik Community

Qlik Design Blog

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

Employee
Employee

Getting all data cells from app.createCube()

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
Not applicable

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?

0 Likes
91 Views
stevenkoppens
New Contributor III

Great post!

Thanks

0 Likes
91 Views
Employee
Employee

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

91 Views
eliasmazur
New Contributor

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

0 Likes
91 Views
andresilva
New Contributor III

Hi Yianni,

Where do I apply getAllResults.js in this api?

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

Thanks,

André Silva

0 Likes
91 Views
rakeshganwanizs
New Contributor

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,

0 Likes
91 Views
andyjalexlive
New Contributor

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
91 Views
Employee
Employee

Yeah this is weird. Try with different dimension

0 Likes
91 Views
andyjalexlive
New Contributor

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
91 Views
Employee
Employee

Change


data.qDataPages[0].qMatrix


to


data[0].qMatrix

0 Likes
91 Views
andyjalexlive
New Contributor

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
91 Views
diegobormann
New Contributor

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
91 Views
eduardo_facchio
New Contributor

Hi Rakesh, do u have any progress in that issue? I want to use this function in JQuery too.

Thank you

0 Likes
91 Views