Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

evgeniyita
Contributor

REST connection: set a path or fill down a value?

Hello,

I'm using REST API + QlikView and I've got 4 endpoints (URLs) which contain the data I need. I know that there is a way how not to keep 4 custom connection statements - it is a "WITH CONNECTION" statement, but...

- Using REST connector's settings we can set a path to a total records parameter

- But using "WITH CONNECTION" statement we have to fill down a value for the total count (but not a path to the field with total)

And it's clear that we don't know the total count of records (count of records can change from time to time). We know just the name of this field.

Is it possible to use path to  the total count of records using WITH CONNECTION statement?


Regards,

Evgeniy

1 Solution

Accepted Solutions
evgeniyita
Contributor

Re: REST connection: set a path or fill down a value?

The solution was pretty clear and we don't need to create a bicycle. Maybe it will be helpful for someone else.

1. Select the separate total value and set up this value to a variable

TotalTable:

SQL SELECT

"__KEY_root",

(SELECT

"total_count",

"__FK_meta"

FROM "meta" FK "__FK_meta")

FROM JSON (wrap on) "root" PK "__KEY_root";

LET total = Peek('total_count',0,'TotalTable');

DROP Table TotalTable;

2. Set up additional variables and use the Offset pagination:

Let startAt = 0;

Let pageSize = 100;

for startAt = 0 to total step pageSize

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

(SELECT

...

WITH CONNECTION(

URL "https://www....",

QUERY "offset" "$(startAt)",

QUERY "limit" "$(pageSize)"

);

NEXT startAt;

1 Reply
evgeniyita
Contributor

Re: REST connection: set a path or fill down a value?

The solution was pretty clear and we don't need to create a bicycle. Maybe it will be helpful for someone else.

1. Select the separate total value and set up this value to a variable

TotalTable:

SQL SELECT

"__KEY_root",

(SELECT

"total_count",

"__FK_meta"

FROM "meta" FK "__FK_meta")

FROM JSON (wrap on) "root" PK "__KEY_root";

LET total = Peek('total_count',0,'TotalTable');

DROP Table TotalTable;

2. Set up additional variables and use the Offset pagination:

Let startAt = 0;

Let pageSize = 100;

for startAt = 0 to total step pageSize

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

(SELECT

...

WITH CONNECTION(

URL "https://www....",

QUERY "offset" "$(startAt)",

QUERY "limit" "$(pageSize)"

);

NEXT startAt;

Community Browser