Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

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.

 

11 Comments
Creator II
Creator II

--

0 Likes
1,016 Views
Employee
Employee

The ElementID is being extracted each pass in the Loop from the Inline table.

So each time the data is read the vElementID contains a new value for the next record.

I believe that the data from each pass will concatenate into the final table.  So when you are done the 100 passes the table will contain all of your data and it should be correct.

I'm not sure what you mean by the data is loose.

0 Likes
1,006 Views
Creator II
Creator II

Hi @MRitter ,

You say:


I believe that the data from each pass will concatenate into the final table.  So when you are done the 100 passes the table will contain all of your data and it should be correct.

 


That is correct, but my question is, when you are done with the 100 passes and you want to analyze the data for each pass, how do you know to which element id the data refers to?

Like, "for the element ID 1sy293, the field values are"... "for the element id h13d13, the field values are...". So, how do we get the column with the element ID in the final table?

0 Likes
996 Views
Employee
Employee

The SQL Select is pulling in the fields that are associated with that element ID used for that call.

I would suggest that if you have real data to try doing a Load and see what you get.  You may have to tweak this code to get exactly what you need.

Or post an example of the "loose" data you are seeing so I can better understand.

0 Likes
982 Views
Creator II
Creator II

--

0 Likes
970 Views
Employee
Employee

Are you able to dissect the data that you have and look at each table to make sure you have what you expected?  So comment out the Join logic and look at what is in each table.

I am not seeing document ID in your data other than the variable being used in the call.  

Can you add the variable to one of your tables (assignedUsers or documentRoles) using an AS statement.  I'm not sure which table it is more relevant for.  The value for this field has to be in your data somewhere.

 

0 Likes
950 Views