Can't get data from an API, pagination problem

[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

[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

[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