topic Load Web File dynamically in Connectivity & Data Prep

Load Web File dynamically

eddysanchez — Wed, 24 Sep 2014 03:11:19 GMT

Hi all

For load a web file in Qlik Sense is mandatory to define the connection with the specific link?

I need to load a web file that the source is made by variables like that:

[https://domain.com/$(vYear)/$(vMonth)/File.XLS]

Any suggestion?

thank you

Att

Eddy

Re: Load Web File dynamically

eddysanchez — Wed, 24 Sep 2014 12:54:33 GMT

If I use the same script of QlikView, don't work and show the next message error:

LOAD statement only works with lib:// paths in this script mode

Re: Load Web File dynamically

Michael_Tarallo — Wed, 24 Sep 2014 13:00:58 GMT

Hi Eddy - in order to use the "legacy" type load statement (without lib) - you must change a setting - in the settings.ini file to disable Standard mode.

The help document is here:

https://help.qlik.com/sense/en-US/online/index.html#../Subsystems/WorkingWith/Content/LoadData/DisableStandardMode.htm?H…

This will enable you to use the http call like it is done in QlikView and with the use of variables.

Otherwise - you can still use variables with the lib statement - BUT you would need to make a connection for each URL.

If you need a sample, please let us know.

Please mark the appropriate replies as helpful / correct so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

Re: Load Web File dynamically

Anonymous — Wed, 24 Sep 2014 13:58:33 GMT

Hi Michael,

The "legacy" mode seems like a good idea to be a "default" one as it gives more flexibility in creating a connection strings to data sources.

Would you pass this suggestion to the team, please?

Best regards,

Janusz

Re: Load Web File dynamically

Michael_Tarallo — Wed, 24 Sep 2014 14:09:26 GMT

Hey Janusz -

Yes - in fact that is the plan - direct from the product manager's mouth:

"We are going to revert the default back to legacy to allow custom connect until the new connectors are available for Sense."

Regards,

Mike

Re: Load Web File dynamically

eddysanchez — Wed, 24 Sep 2014 16:31:12 GMT

Michael, where I can send suggestions for Qlik Sense?

I miss the next shorcut combinations:

Ctrl+E = Data load Editor

Ctrl+R = Load data

Ctrl+Shift+R=Partial Reload (If still exist)

Ctrl+T = Data model viewer

Ctrl+Alt+V = Variables

Att

Eddy

Re: Load Web File dynamically

Michael_Tarallo — Wed, 24 Sep 2014 16:42:15 GMT

Hi Eddy

This is the place for that as well - we will send ideas and suggestions to the R&D team.

In the mean time here are some shortcuts you may find useful where applicable to Qlik Sense.

Standard:

https://help.qlik.com/sense/en-US/online/index.html#../Subsystems/Qlik_Management_Console_help/Content/ServerUserGuide/S…

Additional - (after the gestures)

https://help.qlik.com/sense/en-US/online/index.html#../Subsystems/WorkingWith/Content/UserInterface/GesturesAndKeyboardS…

Regards,

Mike

Re: Load Web File dynamically

chrisbrain — Wed, 08 Oct 2014 12:53:44 GMT

Hi Mike,

This currently looks like a major issue for the many QVSource users out there (and general Qlik users loading from web urls) who rely on being able to construct URLs as the load script runs.

Could you clarify a couple of things from this thread?

You mentioned 'Otherwise - you can still use variables with the lib statement - BUT you would need to make a connection for each URL.'
- What does this mean exactly, can the URL behind a lib statement read variables (initial tests suggest not) or do you need to create a connection for every unique URL?

Yes - in fact that is the plan - direct from the product manager's mouth: "We are going to revert the default back to legacy to allow custom connect until the new connectors are available for Sense."
- This sounds like the reversion back to the legacy mode as default will be temporary, is that right?

Re: Load Web File dynamically

jochem_zw — Wed, 08 Oct 2014 13:00:43 GMT

Hi Mike,

How about Qlik Sense Server, do we also need to change a setting.ini for legacy support?

Where is this setting.ini?

reg. Jochem

Re: Load Web File dynamically

Michael_Tarallo — Wed, 08 Oct 2014 14:09:35 GMT

Hey Chris - nice to hear from you, in regards to your questions:

You mentioned 'Otherwise - you can still use variables with the lib statement - BUT you would need to make a connection for each URL.'

What does this mean exactly, can the URL behind a lib statement read variables (initial tests suggest not) or do you need to create a connection for every unique URL?

If you use the lib statement - it is expecting a defined connection which is read from the list of connections stored in the repository - and if set up with security rules, it will respect those as well. So if a variable is used with the LIB statement such where the variable's values can be something like connection1 or connection2 - the actual connection names connection1 and connection2 must be defined to the correct data sources.

Yes - in fact that is the plan - direct from the product manager's mouth: "We are going to revert the default back to legacy to allow custom connect until the new connectors are available for Sense."

This sounds like the reversion back to the legacy mode as default will be temporary, is that right?

Yes - you are correct, this will happen with the next minor update release, I am being told.

HTH

Mike

Re: Load Web File dynamically

Michael_Tarallo — Wed, 08 Oct 2014 14:11:37 GMT

Hi Jochem,

Yes - though I have not tried it - there is a settings.ini file located here:

C:\ProgramData\Qlik\Sense\Engine

The file may be empty by default and is used for additional settings.

If you have an issue, please let me know.

Mike

Re: Load Web File dynamically

chrisbrain — Wed, 08 Oct 2014 15:07:31 GMT

Hi Mike,

Good to be speaking with you too, especially considering how familiar you are with our product.

I am still not quite clear on this 😕

Regarding:

So if a variable is used with the LIB statement such where the variable's values can be something like connection1 or connection2 - the actual connection names connection1 and connection2 must be defined to the correct data sources.

Can a variable, e.g. $(vSearchTerm), be passed through to a connection or would a connection need to be created for each possible/unique URL?

And:

Yes - you are correct, this will happen with the next minor update release, I am being told.

But it sounded from your initial message that this reversion itself would be temporary, i.e. that again soon after the default will revert to the current position of a 'LIB://' only approach in the script - is that also your understanding?

Re: Load Web File dynamically

Michael_Tarallo — Wed, 08 Oct 2014 15:55:26 GMT

Hi Chris:

Can a variable, e.g. $(vSearchTerm), be passed through to a connection or would a connection need to be created for each possible/unique URL?

If not using the legacy mode AND only using LIB - then each Web File connection-type would need to be defined as shown in the previous reply. The variable could only be the connection name.

If using the Legacy mode - as set in the settings.ini - then you can use the same traditional approach with variables as used with QlikView (the script that QVSource uses)

But it sounded from your initial message that this reversion itself would be temporary, i.e. that again soon after the default will revert to the current position of a 'LIB://' only approach in the script - is that also your understanding?

You are correct, I was not getting into too much detail. It would be temporary change until the full compatibility of connectors and variable support are available for Qlik Sense.

Re: Re: Load Web File dynamically

chrisbrain — Fri, 10 Oct 2014 07:33:11 GMT

So this means that in the default case (when the default mode stabilizes) Qlik users will no longer be able to do something like this?

LET noRows = NoOfRows('Params');
    
for i=0 to $(noRows)-1
     
     let vParam = peek('id', $(i), 'Params');
     
     WebAPI:
     LOAD
        '$(vParam)' as Param,
        col1 as data1,
        col2 as data2
     FROM
     [https://server/api?param=$(vParam)] (XmlSimple, Table is [XXXXXX]
next

Without potentially complex/confusing settings file changes (which may not even be permitted by their organisation)?

This is something which is not only key to QVSource users, but also used commonly in our experience outside of QVSource, which is also evidenced by the OP.

Re: Load Web File dynamically

Fri, 10 Oct 2014 19:25:19 GMT

In Qlik Sense Server v1.0, in the QMC under Configuration > Engine - edit that configuration and you'll find a checkbox that has a label of "Standard". Uncheck that option to provide the "legacy" mode for QlikView you're looking for. That updates the .ini so no need to manually edit a file.

Re: Re: Load Web File dynamically

chrisbrain — Tue, 14 Oct 2014 13:35:24 GMT

Can Qlik comment on the reason for this with respect to loading data from http end points?

The docs state:

For security reasons, Qlik Sense in standard mode does not support absolute or relative paths in the data load script or functions and variables that expose the file system.

However, since absolute and relative paths were supported in QlikView, it is possible to disable standard mode and use legacy mode in order to reuse QlikView load scripts.

Disabling standard mode can create a security risk by exposing the file system.

But this seems related to file system access, not web urls.

Re: Re: Load Web File dynamically

Michael_Tarallo — Tue, 14 Oct 2014 15:54:31 GMT

Hi Chris - all legacy type connectors (including file access and web urls - pre Qlik Sense) that have not been updated yet to take advantage of the new connector architecture (which includes the ability to secure the data connectors with security rules) will fall under this category.

There is a new Web File connector as you can see - when created and used in Standard mode - the ability to create security rules is possible to control access and actions with the defined connections. When these connectors also allow variables to be passed to them, I could see the Legacy mode starting to fade out.

HTH

Mike

Re: Load Web File dynamically

chrisbrain — Wed, 15 Oct 2014 06:55:38 GMT

HI Mike,

So then regarding:

When these connectors also allow variables to be passed to them, I could see the Legacy mode starting to fade out.

Can we assume something like my earlier example will be possible, e.g.:

LET noRows = NoOfRows('Params');
   
for i=0 to $(noRows)-1
    
     let vParam = peek('id', $(i), 'Params');
    
     WebAPI:
     LOAD
        '$(vParam)' as Param,
        col1 as data1,
        col2 as data2
     FROM
     'lib://my api data'
next

Where the 'lib://my api data' will either be able to either access the script variables (e.g. vParam) or there will be some other mechanism to pass them through (i.e. it might not look exactly like the above)?

Re: Load Web File dynamically

Michael_Tarallo — Wed, 15 Oct 2014 11:46:01 GMT

Hi Chris - I will verify with PM

Mike

Re: Load Web File dynamically

jochem_zw — Wed, 15 Oct 2014 18:17:22 GMT

What Chris mentioned is what we absolutely need.

Passing variables is at the moment NOT possible with a LIB connection!

So we have to go back to legacy mode.