Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rbartley
Specialist II
Specialist II

Piwik API - lastX

Hi everyone,

 

I have just begun working on an app to report on PiWik data using a REST connector.  I can connect and return data for a  single date, but when I try to return data by day for the last year, I receive an error:

Date format must be: YYYY-MM-DD, or 'today' or 'yesterday' or any keyword supported by the strtotime function (see http://php.net/strtotime for more information):  

If I copy and paste the URL I am using into my browser, the result returns fine, but when I use the Select Data to load window, I receive the error.

The url is: 

https://<servername>/?module=API&method=VisitsSummary.get&idSite=<site number>&date=last365&period=d...

Any ideas?

 

Thanks in advance

 

Richard

 

 

1 Solution

Accepted Solutions
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @rbartley 

My guess is that Qlik is not happy with the format of the data coming back when it has multiple results in the record set.

What I would suggest is that enumerate around for days 0 to 365, concatenating the results.

To do this you will need to not put the date parameter in the URL and inject it using WITH CONNECTION. This allows you to alter your API call in your load script.

There is help on this here:

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-R...

The code would be something like;

LIB CONNECT TO 'REST Library';

for iDay - 0 to 365
   let sDay = date(today()-iDay, 'YYYY-MM-DD');

    RESTData:
    SQL SELECT
	"Field1",
        "Field2"
    FROM JSON (wrap on) "root" PK "__KEY_root"
    WITH CONNECTION (
      QUERY "date" "$(sDay)"
    );
next

 

To avoid having too many REST connection libraries set up on my servers I tend to just have a 'Generic GET' and a 'Generic PUT' connector which points to a placeholder endpoint and then overwrite the whole URL, headers and parameters using WITH CONNECTION. This keeps things tidier and makes code easier to read.

This blog post describes this in more detail:
https://www.quickintelligence.co.uk/reading-rss-feeds-with-qlik-rest-connector/

Hope that helps.

Steve

 

View solution in original post

4 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @rbartley 

Looking at the PHP documentation I can't see that last365 is supported. You could try last year instead?

Seems strange it would work in a browser and not in the REST connector though.

Are you pasting the entire URL in the URL window, or are you specifying the parameters separately later on in the connector properties?

If you have spaces in the URL (for last year) you may need to put %20 in place of the space.

Have you tried other date periods in there? If so, what results did you get there. 

Steve

rbartley
Specialist II
Specialist II
Author

Hi Steve,

I'm passing the entire URL, including the date and period parameters in the url text box of the REST connection.  I have tried last year (and last%20year),but this gives me summary values for the whole of the last year rather than a break down by day

e.g. lastX gives me something like this.......

<results>
<result date="2020-09-13">
<nb_uniq_visitors>48</nb_uniq_visitors>
<nb_users>0</nb_users>
<nb_visits>61</nb_visits>
<nb_actions>273</nb_actions>
<nb_visits_converted>0</nb_visits_converted>
<bounce_count>22</bounce_count>
<sum_visit_length>18227</sum_visit_length>
<max_actions>26</max_actions>
<bounce_rate>36.07%</bounce_rate>
<nb_actions_per_visit>4.48</nb_actions_per_visit>
<avg_time_on_site>299</avg_time_on_site>
</result>
<result date="2020-09-14">
<nb_uniq_visitors>217</nb_uniq_visitors>
<nb_users>0</nb_users>
<nb_visits>249</nb_visits>
<nb_actions>760</nb_actions>
<nb_visits_converted>0</nb_visits_converted>
<bounce_count>133</bounce_count>
<sum_visit_length>53160</sum_visit_length>
<max_actions>35</max_actions>
<bounce_rate>53.41%</bounce_rate>
<nb_actions_per_visit>3.05</nb_actions_per_visit>
<avg_time_on_site>213</avg_time_on_site>
</result>
 ....

while last year gives me  a single set

<result>
<nb_uniq_visitors>18</nb_uniq_visitors>
<nb_users>0</nb_users>
<nb_visits>18</nb_visits>
<nb_actions>63</nb_actions>
<nb_visits_converted>0</nb_visits_converted>
<bounce_count>7</bounce_count>
<sum_visit_length>8157</sum_visit_length>
<max_actions>16</max_actions>
<bounce_rate>38.89%</bounce_rate>
<nb_actions_per_visit>3.5</nb_actions_per_visit>
<avg_time_on_site>453</avg_time_on_site>
</result>

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @rbartley 

My guess is that Qlik is not happy with the format of the data coming back when it has multiple results in the record set.

What I would suggest is that enumerate around for days 0 to 365, concatenating the results.

To do this you will need to not put the date parameter in the URL and inject it using WITH CONNECTION. This allows you to alter your API call in your load script.

There is help on this here:

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-R...

The code would be something like;

LIB CONNECT TO 'REST Library';

for iDay - 0 to 365
   let sDay = date(today()-iDay, 'YYYY-MM-DD');

    RESTData:
    SQL SELECT
	"Field1",
        "Field2"
    FROM JSON (wrap on) "root" PK "__KEY_root"
    WITH CONNECTION (
      QUERY "date" "$(sDay)"
    );
next

 

To avoid having too many REST connection libraries set up on my servers I tend to just have a 'Generic GET' and a 'Generic PUT' connector which points to a placeholder endpoint and then overwrite the whole URL, headers and parameters using WITH CONNECTION. This keeps things tidier and makes code easier to read.

This blog post describes this in more detail:
https://www.quickintelligence.co.uk/reading-rss-feeds-with-qlik-rest-connector/

Hope that helps.

Steve

 

rbartley
Specialist II
Specialist II
Author

Hi Steve,

Thanks for responding again.  In fact, that's the approach I had taken in the meantime (sorry, I was going to update my post to include this information but you got there before).  The WITH CONNECTION is a great tip and I already use this in another app using a REST connector to connect to the JIRA issue tracking system (I probably found it on your quickintelligence at the time) .

 It's funny that, when I use lastX for the date parameter, directly through the browser, it appears to process the command/interpret the data  in two stages (so that the screen seems to flicker before showing the result).  

Regards,

 

Richard