Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
maxalander
Contributor III
Contributor III

REST connector with automatic date update

Hi,

I'm using the REST connector in Qlik Sense to import data from Mixpanel to Qlik. This connection works fine and gives me the data I want but I have to specify a date range for the API to work properly. This means that I have to manually update the date range to include new data. I want this connection to always fetch the latest data.

In my REST connector I have several parameters, which includes "from_date" and "to_date" (as seen in the picture below).

Screen Shot 2017-08-16 at 16.27.01.png

I'm wondering if it's possible to automatically have the current date in the "to_date" parameter value?

If not, are there other ways do achieve this?

Thanks!

Max

1 Solution

Accepted Solutions
justinvchiang
Contributor III
Contributor III

Hey Max,

One solve for this is to use a variable for your to_date.

1. In your load script, set a variable

LET vToday = date(Today(),'YYYY-MM-DD');

2. Leave the connection query parameters as you have them and then change the query parameter in your load script by using the WITH CONNECTION function which will override what you have stored in your connection:

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION ( 

QUERY "to_date" "$(vToday)")

;

View solution in original post

4 Replies
justinvchiang
Contributor III
Contributor III

Hey Max,

One solve for this is to use a variable for your to_date.

1. In your load script, set a variable

LET vToday = date(Today(),'YYYY-MM-DD');

2. Leave the connection query parameters as you have them and then change the query parameter in your load script by using the WITH CONNECTION function which will override what you have stored in your connection:

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION ( 

QUERY "to_date" "$(vToday)")

;

maxalander
Contributor III
Contributor III
Author

Hi Justin,

this is a great way of solving it!

I have just started to use, and learn how to handle, the WITH CONNECTION in other apps and forgot that I could use the same function here.

Thank you!

marjan_it
Creator III
Creator III

Hi.can you share a qww example file?

I have an api that give date in a range, but I want give it the date yesterday and the date two last day with the function date(now()-1) and date(now()-2). 

I tried your answer but It does not work.

justinvchiang
Contributor III
Contributor III

Hi Marjan_it, did you figure it out?

If you haven't, I don't have any files to share but if your API is able to accept a range as two parameters you would have to list both in the WITH CONNECTION and ensure the date(now()-1) and date(now()-2) values are both in the expected format of the API.  Without any formatting, you'll get MM/DD/YYYY which would fail if the API expected YYYY/MM/DD.