Connection string URL dynamic

[agalli]

Hello guys,

I 've an App with excel as first source connection data, now I need to make a second connection using url for getting data, but this url is dynamic and it changes based on state current user selection so I've saved this selection into variable but I am not able to use it to make a connection.

lib ://CONNECTION is web file linked to 'https://www.myapp.local/value=' 

I 'd like to use it as something like that:

UrlTable:       // already loaded from excel file
LOAD

     ID,
      Value        // where value is column's field that user has selected

resident ListEndOfUrl;

NewUrl:        // new table that I have to make

  LOAD
@1 as Dati,
@2 as Valori
FROM [lib://CONNECTION & 'Value']    //  Obviously It doesn't work , so how can I connect to new url?

I've try to save input "Value" into variable but doesn't work

Someone can help me ?

thanks

[crusader_]

Hi,

You need to add (url is [], html....) after your FROM statement so you'll be able to inject whatever value behind...

Like on below example... my SWIFT_CODES connection points to main page, whereas table stores per country.

vRoot = 'https://www.theswiftcodes.com/';
vCountry = 'afghanistan/';

LOAD
    ID,
    "Bank or Institution",
    City,
    Branch,
    "Swift Code"
FROM [lib://SWIFT_CODES]
(url is [$(vRoot)$(vCountry)], html, utf8, embedded labels, table is @1);

In your case you would need to put your load statement in the loop and peek() values from UrlTable one by one, like below:

FOR i=1 TO NOOFROWS('UrlTable')
	vCurrentUrl = peek('url',$(i)-1,'UrlTable');
        YourData:
        LOAD
        ......
        FROM [lib:/CONNECT]
        (url is [$(vCurrentUrl], html, utf8, .....)

NEXT i;

Hope this helps

//Andrei

[agalli]

Thank you Andrei but your code getting data from all url presents in "UrlTable" (there are over 1.500 rows), whreas I would like to compose url is  based only on user Value selection.

For example, when user make a selection on "Value" in UrlTable  ( where value contains the dynamic part's url) then I need to load data only from this:

$(vCurrentUrl)  =  'https://www.theswiftcodes.com/' + VALUE  (selected by user)

thanks

[crusader_ · Accepted solution]

Hi,

I don't think it's feasible with standard Qlik functionality, unless you do a selection and then press on "reload" button, because data needs to be loaded into memory in advance. If I understood correctly you want some kind of "live" connection to the web source, which is opposite to the concept of "loading data once and not disturbing source anymore" promoted by Qlik.

You may try to load all these pages (over 1500), assuming table structure remains the same and then users will be able to navigate. If your data in changing frequently you might need to think about some kind of incremental load.

Hope this helps.

//Andrei

[agalli]

Hi Andrei,

ok I think the correct way is to load all 1500 pages to VDF (using loop and peek)  and then load incremental data as you said.

What do you think on it?

thanks very much

[crusader_]

Hi,

Well, it's definitely a way forward, however you might need to consider some web-related things like number of requests per second (Qlik Sense will do it pretty fast), if you're using paid API calls to external service - the same thing.

Also think about how often the data changes under each page and how long it takes to make a full load. If it takes 1 minute to load everything and data changes once in hour, probably there is no point to put extra effort into incremental load, if it takes much longer to load everything and/or data changes much more frequently, then you need to give QS indication on what has changed, otherwise it will loop through all 1500 pages anyway...

It's up to you how to proceed with your solution, however always try to keep balance between Effort you put into and Value it gives back.

hope this helps.

//Andrei