Missing records on REST deliver instance

Question

Hi TX Support,

We're encountering a strange issue. We've setup an deliver instance of the REST API endpoint type. The model contains a dimension table. In the MDW layer this table contains 7844 records. Now when we pull the data from the REST endpoint we get 7844 as well, however the content is incorrect. Instead of the 7844 individual records we get 6974 records, 870 of those are duplicated and consequently 870 other records are missing.

Example:

DIM_Location Table

Location, Code, DW_Id

Original MDW data

LocationA, 123, 1

LocationB, 345, 2

LocationC, 678, 3

REST data
LocationA, 123, 1
LocationB, 345, 2

LocationB, 345, 2

Missing Record in the REST data

LocationC, 678, 3

Now I did tried everything I could think of in terms of troubleshooting. Everything is deployed, executed, refreshed and running. Going through the data I made an important observation. The set that is duplicated all are records that reside on the first page. They are then duplicated across the remaining pages. See the screenshot of the 870 duplicated records and their distribution.

Do you have any ideas and/or suggestions on how to solve this issue?

For clarification: we are able to replicate the issue in both Qlik and Postman with the original table and a new test table.

Hilsner,

Luuk

Best answer by Thomas Lind

Hi @luuk.bouman

Just to add what we did to make it work, so others with a similar issue can find a solution.
The available options for this can be seen in the image below.

You need to add pagination when your result has more than 1000 rows.

You can add the parameter $top=-1 to make it pull all rows.

However if you want to still use pagination on your REST API, you need to add a $OrderBy parameter. By default it will use $OrderBy=1 and that is the first field in the endpoint, but it may not be the primary key field. Set the order to be for the primary key field and it will return the correct amount of rows.

Thomas Lind · Accepted Answer

Hi ​@luuk.boumanJust to add what we did to make it work, so others with a similar issue can find a solution.The available options for this can be seen in the image below.You need to add pagination when your result has more than 1000 rows.You can add the parameter$top=-1 to make it pull all rows.However if you want to still use pagination on your REST API, you need to add a$OrderBy parameter. By default it will use $OrderBy=1 and that is the first field in the endpoint, but it may not be the primary key field. Set the order to be for the primary key field and it will return the correct amount of rows.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded