Loop through Table of IDs to Load array from Connection

[prees959]

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

[felipedl · Accepted solution]

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.

[omkarvamsi]

Hi Phil,

Please try as below

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

IDs:

LOAD keyid,

'$(vLink)'& keyid as weblink

INLINE [

    keyid

    1001

    1002

    1003

];

[prees959]

thank you so much for your time!

Phil

[prees959]

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