REST connector: Using WITH CONNECTION

[ArturoMuñoz]

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

[ArturoMuñoz]

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.

[sergeyko]

@beam,

Alright, I looked at your code sample once again, and here's another idea. In each cycle, you need to drop  RestConnectorMasterTable to ensure you are populating it with the new data on the next pass. What I do is dumping the current data from RestConnectorMasterTable into another permanent table so I can safely drop it after that.

So create an empty table prior to entering the loop and then add consolidate RestConnectorMasterTable  data into it using concatenate.

This goes inside your loop:

Concatenate(PermTable)

Load * Resident RestConnectorMasterTable ;

Drop RestConnectorMasterTable ;

[beam]

@sergeyko and everyone else I really appreciate all the help -- but either I'm making a really novice mistake or this just doesn't work 😞 

If I loop just the variables section I get every url as expected:

CODE:

for a = 0 to 2
Let vHostDetail = peek('hostId', $(a),hosts);
Let vURL='https://10.201.216.150/api/hosts/$(vHostDetail)';
Trace $(vHostDetail);
Trace $(vURL);
Next;

OUTPUT:

180959880
https://10.201.216.150/api/hosts/180959880
181025416
https://10.201.216.150/api/hosts/181025416
180959887
https://10.201.216.150/api/hosts/180959887

Once i move the "next" statement below my resident table where my connection statement is used my output looks like this:

180959880
https://10.201.216.150/api/hosts/180959880

https://10.201.216.150/api/hosts/

https://10.201.216.150/api/hosts/

Where only the first ID is populated in the URL. So frustrating.

[jfkinspari]

Have you tried to run a debug and set it to watch the variable vHostDetail, and seen when it changes from containing a value to being empty?

[beam]

@jfkinspari it looks like it turns the vHostDetail to <null> after the second iteration of the loop;

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

At this line variable a = 1, yet the peek pulls back <null> 

[jfkinspari]

Hmm, perhaps you need '' around the table parameter in the peek function

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

[sergeyko]

A couple of other ideas to try.

a) Try Let/Set/None keywords in front of your variable vHostDetail

b) Try building URL as 'https://10.201.216.150/api/hosts/' & $(vHostDetail);

c) Make sure your variable vHostDetail does not get cleared out later in the loop

[Ken_T]

can a where clause be added to this, if so, how is it added so that the WHERE works with the "WITH CONNECTION"

[sergeyko]

@Ken_T - Good question! I don't think WHERE is supported in selecting data from a REST connection. See "SELECT statement syntax" section on the page below.

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-R...

That said, you can try to add additional filters to your URL used in WITH CONNECTION. The same page provides some examples on using URL parameters for pagination.

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-R...

[gary07]

@ArturoMuñoz Hey Arturo, could you please help with the script?

I am trying to use your script. Data load happens without any error, but it doesn't load any values (loads 0 values). I am attaching a  screenshot. Currently there are only two values in [id], but I intend to use a lot more.

Thanks

Gurinder

[Ken_T]

Does WITH CONNECTION
only work for REST connection?

can WITH CONNECTION be used with any other connection types?