topic Re: Qlik Sense - Qlik Engine API - .NET SDK - Result too large in Integration, Extension & APIs

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

alessandrovernile — Thu, 31 Aug 2023 16:54:02 GMT

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

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

Øystein_Kolsrud — Fri, 01 Sep 2023 07:38:18 GMT

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

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.DataPager.GetAllData.htm#Qlik_Engine_DataPager_GetAllData_System_Int32_

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

alessandrovernile — Mon, 04 Sep 2023 10:19:52 GMT

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

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

Øystein_Kolsrud — Mon, 04 Sep 2023 12:52:19 GMT

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());

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

alessandrovernile — Mon, 04 Sep 2023 13:15:04 GMT

Ok thanks,

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

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

Øystein_Kolsrud — Mon, 04 Sep 2023 13:31:16 GMT

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.