Re: Can't get data from an API, pagination problem - Qlik Community

dbernal_007

Hi everyone, I tried with page, and Token pagination types and it doesn't works. Get the first 100 records and is not possible to get 1600 rows, the total quantity of rows.

This is an example of the raw rows that I get in postman:

{
"limit": 100,
"page": 1,
"prevPageToken": "CN653AEaCSnDt9zXmgEAABoOWgxpLQVNWO19uYEB8jMiDloMaS0FTVjtfbmBAfIz",
"nextPageToken": "CPab5gEaCSluwYbZmgEAABoOWgxpLXJejfJHRHYorGciDloMaS1yXo3yR0R2KKxn",
"totalDocs": 6124,
"totalPages": 62,
"hasNextPage": true,
"hasPrevPage": false,
"nextPage": 2,
"prevPage": null,
"pagingCounter": 1
}

But you know that Qlik starts from docs table, not from the raw.

What is the exact parameter configuration that I should put into the REST connector?

Thanks for your replies.

marksouzacosta

Hi @dbernal_007,

Where is this API from? Do you have the documentation?
Paginations can be implemented in very different ways, so we need this REST API documentation to figure out how to do the pagination.

This is one example of Token pagination:

Regards,

Mark Costa

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

dbernal_007

Hi Mark, yes I have the documentation, do you want that send to you?

DavidDocquoyBaudelet

J'ai eu le même genre de souci pour lire des API provenant du CRM SELLSY.

La solution que j'ai trouvé est la suivante :

Si le 1er appel permets de savoir qu'il y a 62 pages, alors je fais une boucle qui se répètera 62 fois, et je lis à chaque fois une page différente.

Dans l'appel API tu dois avoir un paramètre qui te permets

Soit de passer le no de page que tu veux
soit les lignes debuts et fins (Dans ce cas, tu as juste a gérer un compteur incrémental de 100 en 100)
Soit une ligne debut seule (1er appel, à partir de la ligne 1, 2eme à partir de la ligne 101, 3eme à partir de la ligne 201, etc.)

Voila quelques pistes sur comment ca peux fonctionner.

PrashantSangle

Can you share your data loading script for this API?

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

DavidDocquoyBaudelet

Je vais essayer de transcrire mon script.

1 - Récupération du Token de connexion

LIB CONNECT TO 'REST_Sellsy.tokens';

RestConnectorMasterTable:
SQL SELECT 
	"token_type",
	"expires_in",
	"access_token"
FROM JSON (wrap on) "root";

[root]:
LOAD	[token_type],
	[expires_in],
	[access_token]
RESIDENT RestConnectorMasterTable;

let vAuthorization=Peek('access_token',0,'root');

2 - Connexion à l'API de lecture des données pour récupérer les paramètres (Limite de chargement, Nombre de ligne)

RestConnectorMasterTable:
SQL SELECT 
"__KEY_root",
(SELECT 
"limit",
"count",
"total",
"offset",
"__FK_pagination"
FROM "pagination" FK "__FK_pagination")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "https://api.sellsy.com/v2/companies",
HTTPHEADER  "Authorization" "Bearer $(vAuthorization)"
);


[pagination]:
LOAD [limit],
[count],
[total],
[offset],
[__FK_pagination] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_pagination]);

DROP TABLE RestConnectorMasterTable;

3 - Calcul du nombre total de ligne et du nombre de boucle à réaliser

let vRecordTotal=Peek('total',0,'pagination');
let vBoucleTotal=round(Peek('total',0,'pagination')/100);

4 - Boucle de lecture

For i = 1 to $(vBoucleTotal)+1

if $(i)=1 then 
let j=$(i)-1;
endif

If NoOfRows('RestConnectorMasterTable') > 0 then
    DROP TABLE RestConnectorMasterTable;
end if


RestConnectorMasterTable:
SQL SELECT 
"id" AS "id_u0",
"name",
"__KEY_data",
FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION (
URL "https://api.sellsy.com/v2/companies",
QUERY "limit" "100",
QUERY "offset" "$(j)",
HTTPHEADER  "Authorization" "Bearer $(vAuthorization)"
);

concatenate (COMPANIES)
LOAD [id_u0] AS [id_u0],
[name]
    ($(i)-1)*100 + [__KEY_data] as [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_data]);

Let j = (($(i))*100);

Next

En espérant que cela puisse vous aider.

marksouzacosta

Yes please. If you can share here in the post, will be even better so others can also take a look.

If not, you can send me privately.

Regards,

Mark Costa

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

PrashantSangle

Hi, When I saw the output of API in your original post. I believe you don't required for loop in script. instead of this use pagination > Next Page > & provide appropriate path

If still it is not working for you. then can you share output (screenshot) of Root like below screen

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

dbernal_007

Hi Phrashant,

This is the script. I planned this workaround because the parameters on the REST connection doesn't works.

In this script the error is "Table Orders_Full doesn't exists".

LET vStartYear = 2025;

LET vEndYear = 2025;

IF $(vQvdExists) = 0 THEN

TRACE *** FULL LOAD Be-Flow por MESES ***;

FOR vYear = $(vStartYear) TO $(vEndYear)

FOR vMonth = 1 TO 12

// Construir fechas del mes

LET vFrom = Date(MakeDate($(vYear), $(vMonth), 1), 'YYYY-MM-DD');

LET vTo = Date(MonthEnd(MakeDate($(vYear), $(vMonth), 1)), 'YYYY-MM-DD');

TRACE Cargando rango $(vFrom) → $(vTo);

RestConnectorMasterTable:

SQL SELECT "_id" AS "_id_u7",

"merchant_id",

"remote_order_id" AS "remote_order_id_u0",

.

"folio",

"__FK_documents"

FROM "documents" FK "__FK_documents")

FROM JSON (wrap on) "docs"

WITH CONNECTION (

URL "https://api.be-flow.com/api/orders/v2?limit=100&date_range[0]=$(vFrom)&date_range[1]=$(vTo)"

);

// Cargar docs del día

Orders_Month:

LOAD

_id,

merchant_id,

remote_order_id,

location_id,

name,

currency,

total_price,

total_discount,

total_shipping,

total_tax,

order_status,

sac_status,

fulfillment_method,

createdAt,

updatedAt,

Timestamp(createdAt) as createdAt_TS,

Timestamp(updatedAt) as updatedAt_TS,

'$(vFrom)' & ' → ' & '$(vTo)' as loadRange

RESIDENT RestConnectorMasterTable;

// Solo si hay filas en Orders_Day

IF NoOfRows('Orders_Month') > 0 THEN

// Si Orders_Full NO existe → crearla

IF NOT TableNumber('Orders_Month') >= 0 THEN

Orders_Full:

NOCONCATENATE

LOAD *

RESIDENT Orders_Month;

// Si Orders_Full ya existe → concatenar

ELSE

CONCATENATE (Orders_Full)

LOAD *

RESIDENT Orders_Month;

END IF

// Limpiar temporales del día

DROP TABLE RestConnectorMasterTable;

DROP TABLE Orders_Month;

NEXT vMonth

NEXT vYear

// Guardar QVD inicial

STORE Orders_Full INTO $(vQvdFile) (qvd);

// Limpiar

DROP TABLE Orders_Full;

TRACE *** FULL LOAD Be-Flow COMPLETADO ***;

END IF;

------------------------------------------------------------------------------------

Regards,

Daniel

dbernal_007

First at all, thank you David for your great explanation.

But the script didn't work because the first root read it doesn't retrieve any row:

RestConnectorMasterTable << RestConnectorMasterTable

Lines fetched: 0

root << RestConnectorMasterTable

Lines fetched: 0

After this, the second block falls:

HTTP protocol error 500 (Internal Server Error):

{"message":null}

The error occurred here:

RestConnectorMasterTable:

SQL SELECT "__KEY_root",

(SELECT "limit", "count", "total", "offset", "__FK_pagination" FROM "pagination" FK "__FK_pagination") FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION ( URL "https://api.be-flow.com/api/orders/v2?limit=100&date_range[0]='2025-12-31'&date_range[1]='2025-01-01'",

HTTPHEADER "Authorization" "Bearer " )

So thank you anyway.

Daniel

Can't get data from an API, pagination problem

API

Connector

Qlik Sense