Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

hello_wisely
New Contributor

Dynamic end date web connector

Hello everybody,

I'm currently designing applications with data coming from the data web connectors but you always need to specify a specific end date. I would like to have a dynamic time range in which the web connector always retrieves the most recent data.

Anybody an idea how to do this?

Thanks in advance,

Sam

10 Replies
hello_wisely
New Contributor

Re: Dynamic end date web connector

I am talking about the Facebook Insight web connector.

MVP & Luminary
MVP & Luminary

Re: Dynamic end date web connector

You can convert from the system clock into a string like this:

let vEnd = Date(DayEnd(Now()), 'YYYYMMDDhhmmss');

let vStart = Date(DayStart(Now()-28), 'YYYYMMDDhhmmss');

You will need to change the format mask to match what Facebook is expecting.

You can then insert the variable into the load script in place of the date like this $(vEnd) and $(vStart).


If you are using Qlik Sense in Standard mode you will need to be using a recent version of sense (Feb 18 on, I think) and you can then use the WITH CONNECTION function to modify the dates from the defaults you put in when you create the connection.

If you search this forum you will find more details on WITH CONNECTION.

Hope that helps,
Steve

hello_wisely
New Contributor

Re: Dynamic end date web connector

I'm normally using the URL connection. My qlik sense is up to date and i'm using the standard mode. The end date is always specified in the URL where to get the data. Doesn't this influence the time?

MVP & Luminary
MVP & Luminary

Re: Dynamic end date web connector

In that case it should be quite straight forward:

let vEnd = Date(DayEnd(Now()), 'DD-MM-YYYY');

let vStart = Date(DayStart(Now()-28), 'DD-MM-YYYY');


...

FROM ....&startDate=$(vStart)&endDate=$(vEnd)&period=day...


Hope that works for you.


Steve

hello_wisely
New Contributor

Re: Dynamic end date web connector

I never have to go in the script, I just enter the URL after which the data is added.

If I then after loading the data go to the script editor and put in the Let commands they give me an error and by then I can not edit the URL anymore. If I'm correct to use the script editor you need to disable standard mode which isn't possible in the Qlik Sense server version (the one i'm using)

MVP & Luminary
MVP & Luminary

Re: Dynamic end date web connector

Hi,

It is worth getting used to using the Data Load Editor, as there are some things that you can do there which are impossible in the Data Manager.

The details of how you can have a URL into which you can inject parameters is given here:

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/DataSource/load-data-from-file...

To use this go to the Data Load Editor after bringing in your table in the Data Manager.  You will need to unlock the script (note this will stop you using the Data Manager again in that app).

You should see a statement loading from a Library, like this:

LOAD

    FieldList

FROM [lib://GS]

(html, utf8, embedded labels, table is @1);

In front of the file format specifier you need to enter URL IS and the URL you want to load from, with variables:

LOAD

    FieldList

FROM [lib://GS]

(URL IS [https://www.somesite.com/getdata.asp?Param1=123&startDate=$(vStart)&endDate=$(vEnd)&period=day], html, utf8, embedded labels, table is @1);

You may find some tweaking is required - but hopefully this will put you on the right track.

Steve

hello_wisely
New Contributor

Re: Dynamic end date web connector

Thanks, I'm trying this atm but i'm getting errors that connection GS is not found

MVP & Luminary
MVP & Luminary

Re: Dynamic end date web connector

Look on the right hand side of the load script, you should see a connection there.  Copy the name of that in place of the GS name in my script.  GS is the name that Sense gave me when I created a web connection in Data Manager - it may be that Sense picks something random here?

hello_wisely
New Contributor

Re: Dynamic end date web connector

FacebookInsightsConnector_page_fans:

Let vEnd = Date(DayEnd(Now()), 'DD-MM-YYYY');

Let vStart = Date(DayStart(Now()-28), 'DD-MM-YYYY');

LOAD

    id as page_fans_id,

    name as page_fans_name,

    title as page_fans_title,

    period as page_fans_period,

    end_time as page_fans_end_time,

    Timestamp(timestamp#(end_time, 'YYYY-MM-DDThh:mm:ss+0000')) as page_fans_end_time_qlik_timestamp,

    Date(date#(subfield(end_time, 'T', 1), 'YYYY-MM-DD')) as page_fans_end_time_qlik_date,

    end_date_pacific as page_fans_end_date_pacific,

    Date(date#(end_date_pacific, 'YYYY-MM-DD')) as page_fans_end_date_pacific_qlik_date,

    sub_name as page_fans_sub_name,

    value as page_fans_value,

    description as page_fans_description

FROM

[http://localhost:5555/data?connectorID=FacebookInsightsConnector&table=page_fans&pageId=XXXXXXX&star...]

(qvx);

This is the code I use in the load script. I've also put Qlik Sense into the Legacy mode but still getting the lib:// error in here.

Now it says "Field 'id' not found"