topic Re: Loop through Table of IDs to Load array from Connection in QlikView

Loop through Table of IDs to Load array from Connection

prees959 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a resident table of Key_ID. I also have a REST connection with a url of https://api-mydomain.com/customers/

I need to create a loop whereby I cycle through each ID in Key_ID and run the REST Connection like so

First ID 786625555744

Connection URL becomes - https://api-mydomain.com/customers/786625555744

Second ID 786625478998

Connection URL becomes - https://api-mydomain.com/customers/786625478998

....and so on until the all the IDs in Key_ID have been used.

I'm guessing I need to use a variable like so : Connection URL becomes - https://api-mydomain.com/customers/$(varKeyID)

but I am completely unsure of the syntaxt or how to set up such a loop.

Can anyone help please?

Thanks,

Phil

Re: Loop through Table of IDs to Load array from Connection

felipedl — Wed, 16 Aug 2017 13:58:20 GMT

Hi Phil,

As an example in QlikView, i'm using the REST Api from https://jsonplaceholder.typicode.com

The bolded $(i) iterates through the posts in the API, attached an example.

let vURL = 'https://jsonplaceholder.typicode.com/posts/';

for i = 1 to 3

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL)$(i);timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=None;XUserId=EMACMDB;XPassword=IQVUBcB;";

RestConnectorMasterTable:

SQL SELECT

"userId",

"id",

"title",

"body"

FROM JSON (wrap on) "root";

[root]:

LOAD [userId],

[id],

[title],

[body]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

next i;

Felipe.

Re: Loop through Table of IDs to Load array from Connection

omkarvamsi — Wed, 16 Aug 2017 14:21:36 GMT

Hi Phil,

Please try as below

Let vLink='http:domain.com\';

IDs:

LOAD keyid,

'$(vLink)'& keyid as weblink

INLINE [

keyid

1001

1002

1003

];

Re: Loop through Table of IDs to Load array from Connection

prees959 — Wed, 23 Aug 2017 05:48:26 GMT

thank you so much for your time!

Phil

Re: Loop through Table of IDs to Load array from Connection

prees959 — Wed, 23 Aug 2017 05:51:00 GMT

In addition to this query, I have been given an API where it only brings back the first 30 records and I need to use pagination. Is there any way simple way to achieve this?

Many thanks again,

Phil