Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prees959
Creator II
Creator II

Loop through Table of IDs to Load array from Connection

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

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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.

View solution in original post

4 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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

Hi Phil,

Please try as below

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

IDs:

LOAD keyid,

'$(vLink)'& keyid as weblink

INLINE [

    keyid

    1001

    1002

    1003

];

prees959
Creator II
Creator II
Author

thank you so much for your time!

Phil

prees959
Creator II
Creator II
Author

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