Qlik Sense - Qlik Engine API - .NET SDK - Result too large

[alessandrovernile]

Hi,

I'm trying to get more than 10 columns of data from Qlik sense but it give me this error:

Here's the main method to retrieve the data filtered by column based a user input  

int startIndex = i * _noSqlInsertChunkSize;

// This method filter data by columns name input
var nxPages = GetQlikSensePagedQuery(columnInfo, dataColumns, startIndex, _noSqlInsertChunkSize);

// This is the method that gives the error
var dataPages = await wrappedObject.GetPagedDataAsync(nxPages);

This is the method to filter data by column name:

private IEnumerable<NxPage> GetQlikSensePagedQuery(List<ColumnInfo> columnInfo, List<DataColumnCdto> dataColumns, int startIndex, int chunkSize)
        {
            var result = new List<NxPage>();

            for (int i = 0; i < columnInfo.Count(); i++)
            {
                var currentCI = columnInfo[i];
                if (dataColumns.Any(dc => dc.PropertyName == currentCI.Name))
                {
                    result.Add(new NxPage { Top = startIndex, Height = chunkSize, Left = i, Width = 1 });
                }
            }
            return result;
        }

This is the method that gives the error:

public async Task<IEnumerable<NxDataPage>> GetPagedDataAsync(IEnumerable<NxPage> nxPages)
{
            try
            {
                //this is the part that gives error
                return await _table.GetDataAsync(nxPages);
            }
            catch(MethodInvocationException e)
            {
                var details = e.InvocationError;
                switch (details.Code)
                {
                    case NxLocalizedErrorCode.LOCERR_HC_RESULT_TOO_LARGE:
                    throw new Exception("Qlik Sense doesn't support the number of columns used in the datasource. Please remove some of them.");
                }
                throw;
            }
        }

Is there a solution to solve this error?

We use Qlik Sense .NET SDK 13.7.0

Thanks

[Øystein_Kolsrud]

A page is not allowed to contain more than 10000 cells, and it sounds like you are running into that limit. So my recommendation is for you to check what the page size is, and if it is indeed larger than 10k cells, then implement some form of pagination to get the data in chunks!

That's a rather old .NET SDK version you're on though. There are some convenient functions for doing pagination in newer versions of the library:

https://help.qlik.com/en-US/sense-developer/August2023/Subsystems/NetSDKAPIref/Content/Qlik.Engine.D...

That class for instance has this method that can be a convenient way to page through all data:

https://help.qlik.com/en-US/sense-developer/August2023/Subsystems/NetSDKAPIref/Content/Qlik.Engine.D...

[alessandrovernile]

Thank you,

I used this code to retrieve paginated results:

var o = application.GetGenericObject(objectsApp.Name);
var pager = o.GetAllHyperCubePagers().First();

var allPages = pager.IteratePages(new[] { new NxPage { Width = pager.NumberOfColumns, Height = 100 } }, Pager.Next).Select(pageSet => pageSet.Single());
                               
var pages = allPages.SelectMany(page => page.Matrix).ToList();

 Is there a way to retrieve already filtered data by column? Because using this api the whole table is returned to me.

Thank you

[Øystein_Kolsrud]

How many columns does your table have? For that code to achieve page sizes of 10k cells you would have to have at least 100. Which sounds like a lot...

But you can certainly filter by column. If you set page with to a fixed number (like 10), then you can use the "Left" property of the NxPage instance to choose which set of columns to get. For instance, this call would get only columns 11 through 20:

var allPages = pager.IteratePages(new[] { new NxPage { Width = 10, Left = 11, Height = 100 } }, Pager.Next).Select(pageSet => pageSet.Single());

[alessandrovernile]

Ok thanks,

but is it possible to filter columns to retrieve by column name?

[Øystein_Kolsrud · Accepted solution]

No, that is not something the engine API provides out of the box. You would have to figure out the column numbers based on the column names yourself, and then build your NxPage instances accordingly.