Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Loading XML files from web via REST connector

Luminary
Luminary

Loading XML files from web via REST connector

Since Qlik Sense introduced data connections it was imposible to load multiple tables from web source in a automated way and you needed a LIB data connection for each and every source table. Recently I was asked to migrate from QlikView to Qlik Sense an app which was processing tens of xml files containing exchange rates. Every month there was a new xml file and I wanted to avoid creating a data connection each month, so I was trying this and that. The source was a xml file and I remembered that REST Connector is able to process xml data, so I wondered: what if I use URL of that file to create a REST data connection and then use WITH CONNECTION parameter to change the source URL dynamically?

And, it did work! So here's how to do it.

First, you need to create a REST data connection to an URL. In my case it was

http://www.nbs.sk/_img/Documents/_KurzovyListok/KLVCM/EX201710.XML

This file resides on the website of the Slovak Central Bank and it contains exchange rates of so called "exotic currencies". Look at the file name. It consists of year and month for which the file is valid. And I needed to load all files starting from January 2009. But back to the data connection. Basically, the only thing I needed to set here (apart from URL) was Authentication and I set it to Anonymous, since we're not really authenticating anywhere.

After creating a connection I created a request (in fact two requests, see attached app) to load data. Next step was to create a loop which will go through all months starting from January 2009 until today and create a correct URL, which will be later used in the WITH CONNECTION parameter of the load statement.

mmax = month(today(0));

ymax = year(today(0));

for vYear =2009 to ymax

    for vMonth = 1 to 12

        if (vYear * 12 + vMonth) <= (ymax * 12 + mmax) then

            vDateNo = vYear * 100 + vMonth

            vFile= 'http://www.nbs.sk/_img/Documents/_KurzovyList‌ok/KLVCM/EX' & vDateNo & '_en.XML';

//LOAD statement goes here

          EndIf

     Next vMonth

Next vYear      

The last step was to use the WITH CONNECTION parameter for the REST call. Basically, WITH CONNECTION allows you to customize your REST calls, adding query parameters, and changing parameters, including the target URL (see https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/1.2/Load-REST-data/Sel... for details). So within the loop I used two REST calls, one of them follows:

SQL SELECT

    "validFrom",

    "number",

    "__KEY_nbsExtRateList",

    (SELECT

    FROM "rateList")

FROM XML "nbsExtRateList" PK "__KEY_nbsExtRateList" QDL

WITH CONNECTION (Url "$(vFile)");

Almost done, I just needed to join tables resulting from the two REST calls. See attached app for full script.

Note 1: Since REST connector can process xml, csv and json I suppose it is possible to only load these sources in this manner. So no html tables as it was possible in QlikView.

Note 2: This will be soon invalidated by the February 2018 release of Qlik Sense, which will introduce dynamic URLs for web files, so it will be much easier to load data from web in loops. Until then, enjoy this workaround!

EDIT: Adding Note 3 based on comment by Steve Dark (stevedark‌)

Note 3: It is possible to enable the functionality where you can do everything you can in a QlikView load script. In Desktop this is modifying a config file, in Enterprise it is in QMC, untick Standard Mode in the Engine settings (Editing an engine ‒ Qlik Sense). You need to be very aware of the security implications of disabling this property though, as it will mean that anyone who can edit script can access anywhere on the servers file system that the service account has access to (as access is delegated through the service).

Attachments
Comments
Luminary
Luminary

Makes sense and simple ty.

MVP
MVP

This is a neat and useful solution, as I have seen people asking for this functionality previously.  Thanks for sharing,

The WITH CONNECTION property of the REST connection is very useful, I tend to have two set up one for GET and one for POST - both pointing to dummy endpoints.  Everything can then be overwritten as required.


Just to add a Note 3, it is possible to enable the functionality where you can do everything you can in a QlikView load script.  In Desktop this is modifying a config file, in Enterprise it is in QMC, untick Standard Mode in the Engine settings (Editing an engine ‒ Qlik Sense).

You need to be very aware of the security implications of disabling this property though, as it will mean that anyone who can edit script can access anywhere on the servers file system that the service account has access to (as access is delegated through the service).

All good fun.

Luminary
Luminary

Hi Steve,

Thanks for stopping by and thanks mentioning the "Legacy Mode" as well. I thought about including it in the post, then changed my mind several times, and now I think I should have mentioned it. I will update the post with Note 3 and credit you once I am able to do so (for some reason I am not able to edit published post, Jive support should be looking at it).

Cheers.

Version history
Revision #:
1 of 1
Last update:
‎01-17-2018 04:11 PM
Updated by: