Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
Creator
Creator

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;

View solution in original post

1 Reply
Highlighted
Creator
Creator

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;

View solution in original post