Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
ArturoMuñoz
Employee
Employee

See what WITH CONNECTION can do for you when working with REST data sources

I was approached by a colleague with a simple but recurrent question, how can I use for…while loops in conjunction with the REST connector, and how can we make it work with the connection library that comes with Qlik Sense.

 

A few months ago I shared a “hack” so we can do loops in our scripts as we do with QlikView, activating Legacy Mode will make connection library optional, please remember that there are a bunch of good reasons for not do that.  “With connection” is a great solution for those of you who don’t want or just can’t activate Qlik Sense Desktop to work in Legacy Mode but still need to loop through several URLs to get complete data.

 

Let’s do a quick example.

 

We need to load data from an online data source, we have a URL to connect to the data that contains the ID of each one of the elements that we would like to load into our app.

 

https://www.domain.com/element?key=elementID/more_stuff

 

We have a list of 100 alphanumeric elementIDs and we need to make sure we load them all. Using our REST connector, we could easily connect to the online data source and extract the data,  but we faced the issue of how to make to connection loop trough a series of items.  

In our example we have an inline table that contains each one of the ID numbers of the elements we want to read in the REST connector, and we need to pass that parameter to the connection URL below.

 

LIB CONNECT TO 'REST';

 
ElementsToLoad:

Load * inline [

ElementID

12fa91

1sy293

h13d13

…

];


Let j=0; for j = 0 to 99 Let vElementID = peek('ElementID', $(j), 'Teams'); RestConnectorMasterTable: SQL SELECT     fields     FROM "datad") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION(Url "https://www.domain.com/element?key=$(vElementID)/more_stuff/"); NEXT j; DROP TABLE RestConnectorMasterTable; exit Script;

 

After the inline statement, we loop over the REST connection 100 times, one time for each row of the inline table, I know we have 100 rows so I'm hard coding that number but if you don't know how long your table is you should check that before and store it in a variable.

 

Lately, using "With Connection" we have access to the data source URL so we can expand my variable "vElementID" containing the necessary ID for the connection to work.

 

More info about "With Connection" here -> https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-R...

 

Arturo @arturoqv

I wish you all a great end of the year and a very successful 2019.

 

32 Comments
sergeyko
Partner - Contributor III
Partner - Contributor III

Disregard the entire line where it says "datad", the following line includes the essential "FROM JSON ..." statement.

0 Likes
1,343 Views
Anonymous
Not applicable

How does it work when basic authentication is required? It seems that the user + password are reset when you change the URL. 

WITH CONNECTION(
  Url "$(url)",
  HTTPHEADER "Authorization" "Basic $(username):$(password)"
)

doesn't work either.

0 Likes
1,286 Views
MRitter
Employee
Employee

What I have had to do in the past is make a first call in your load script to get the Authentication Key.  Save that in a variable.

Then in your With Connection you use that variable for the key value.

In the REST Connector itself you enter the username and password into the properties for that connection. I had an admin login that I used that never changed in my situation.

I don't know enough about your situation to say if this will work for you or not.

0 Likes
1,402 Views
beam
Contributor II
Contributor II

This was really a terrific article and exactly what I was looking for however it isn't quite working. Instead of inline load I'm loading from a table previously generated and trying to loop through the data in the table. I see the new data generated but it is not incrementing it simply is grabbing the same data however many times I specify in the statement for x( 'for j = 0 to x' ).

hostId is a previously generated table with Ids of hosts I'm trying to iterate through with my connection url.

I know it is working because if I substitute in hard coded 0,1,2 as "a". It pulls three different hostIds. Using the scripted example above to loop only brings back the same hostID 3 times.

 

 

LIB CONNECT TO 'GETDETAILS';

for a = 0 to 2

Let vHostDetail = peek('hostId', a);

RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"ip",
"mac",
"id",
"__KEY_host",
"__FK_host",
(SELECT
"__KEY_fields",
"__FK_fields",
(SELECT
"timestamp" AS "timestamp_u38",
"value" AS "value_u38",
"__FK_matched_fingerprints"
FROM "matched_fingerprints" FK "__FK_matched_fingerprints"),
(SELECT
"timestamp" AS "timestamp_u76",
"value" AS "value_u76",
"__FK_engine_seen_packet"
FROM "engine_seen_packet" FK "__FK_engine_seen_packet")
FROM "fields" PK "__KEY_fields" FK "__FK_fields")
FROM "host" PK "__KEY_host" FK "__FK_host"),
(SELECT
"__KEY__links",
"__FK__links",
(SELECT
"href",
"templated",
"__FK_self"
FROM "self" FK "__FK_self")
FROM "_links" PK "__KEY__links" FK "__FK__links")
FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (
Url "https://10.201.216.150/api/hosts/$(vHostDetail)",
HTTPHEADER "Authorization" "$(api_key)")
;


Next;

 

 

 

 

ANY HELP WOULD BE EXTREMELY APPRECIATED!

1,339 Views
Anonymous
Not applicable

@beam  I guess it's because you load over the same table "RestConnectorMasterTable" that gets refreshed on every new host id. You should probably do a CONCATENATE.

 

1,315 Views
beam
Contributor II
Contributor II

Hmm I don't think so the table I'm loading from is 'hosts' in a previous script. I can hardcode the 'a' value and pull back any of the IDs in the table 'hosts' just fine. Sorry if I'm not fully understanding your explanation I'm still VERY new to qlik.

 

[hosts]:
LOAD [hostId] AS [hostId],
__KEY_hosts
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_hosts]);
JOIN(hosts)
[_links]:
LOAD [__KEY__links] AS [__KEY__links],
[__FK__links] AS [__KEY_hosts]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK__links]);
JOIN(hosts)
[self]:
LOAD [href] AS [href],
[templated] AS [templated],
[__FK_self] AS [__KEY__links]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_self]);

0 Likes
1,292 Views
beam
Contributor II
Contributor II

@Anonymous any ideas?

I've just seen this similar issue on another post from 2 years ago and no solution. Is this some sort of bug?

https://community.qlik.com/t5/QlikView-App-Development/REST-Connection-Dynamic-RestConnectorMasterTable/td-p/85188

 

 

1,230 Views
sergeyko
Partner - Contributor III
Partner - Contributor III

Hi @beam ,

Try something like:

for a = 0 to 2

Let vHostDetail = peek('hostId', $(a));

 

I have a similar loop working in my app.

0 Likes
1,196 Views
beam
Contributor II
Contributor II

@sergeyko thanks but still doesn't work. It is weird if I just loop the top part it pulls the three urls just fine. Once I use the url inside the "with connection" statement and loop after my sql select it only takes the first url.

0 Likes
1,318 Views
jfkinspari
Partner - Specialist
Partner - Specialist

@beam 

Does it actually use the first url from the loop, or does it use the default url in the connection?

In case of the latter, it could be that it's a syntax error

1,291 Views