topic Re: Dynamic end date web connector in Connectivity & Data Prep

Dynamic end date web connector

Anonymous — Fri, 02 Apr 2021 07:39:05 GMT

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

Re: Dynamic end date web connector

Anonymous — Fri, 27 Apr 2018 11:38:53 GMT

I am talking about the Facebook Insight web connector.

Re: Dynamic end date web connector

stevedark — Fri, 27 Apr 2018 11:51:57 GMT

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

Re: Dynamic end date web connector

Anonymous — Fri, 27 Apr 2018 12:15:37 GMT

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?

Re: Dynamic end date web connector

stevedark — Fri, 27 Apr 2018 13:37:34 GMT

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

Re: Dynamic end date web connector

Anonymous — Fri, 27 Apr 2018 14:31:00 GMT

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)

Re: Dynamic end date web connector

stevedark — Sat, 28 Apr 2018 17:47:40 GMT

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-files.htm#Web

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

Re: Dynamic end date web connector

Anonymous — Thu, 03 May 2018 06:51:56 GMT

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

Re: Dynamic end date web connector

stevedark — Thu, 03 May 2018 07:07:37 GMT

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?

Re: Dynamic end date web connector

Anonymous — Thu, 03 May 2018 09:01:22 GMT

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&startDate=$(vStart)&endDate=$(vEnd)&period=lifetime&appID=]

(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"

Re: Dynamic end date web connector

stevedark — Mon, 07 May 2018 23:37:36 GMT

Try copying and pasting the URL into the browser on the Qlik server, you may get more feedback there.

To ensure you get the URL you are trying in Sense add a line;

let vURL = 'http://localhost:5555/data?connectorID=FacebookInsightsConnector&table=page_fans&pageId=XXXXXXX&startDate=$(vStart)&endDate=$(vEnd)&period=lifetime&appID=';

You can then find the variable vURL in the Sense UI and copy and paste the content into a browser.