Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QV Source and Google Analytics load Data for last month till now?


Hi everybody,

how is it possible to load Data via QVSource for several past months till now. It is possible to make a query via Query Explorer for a time span. But I would like always to have data untill the actual day. So how is it possible to do it with QVSource? Can you please share experience and examples?

Thank you,

4 Replies
chrisbrain
Specialist II
Specialist II

Hi Abdul,

The input parameters you enter into QVSource almost always appear in the generated load script which you then paste into your QlikView/Qlik Sense application.

So for example, you might generate the following feed URL in the Google Analytics Query Explorer:

www.googleapis.com/analytics/v3/data/ga?ids=ga%3AXXXXXX&dimensions=ga%3ApagePath%2Cga%3Adate&metrics=ga%3Abounces&start-date=2015-01-06&end-date=2015-01-20&max-results=50

This URL then appears in the generated load script (although it's URL encoded and only includes the part after the ?):

GoogleAnalyticsConnectorV3_DataFromQueryURI:

LOAD

  dim_pagePath as DataFromQueryURI_dim_pagePath,

  dim_date as DataFromQueryURI_dim_date,

  makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as DataFromQueryURI_date_qv,

  metric_bounces as DataFromQueryURI_metric_bounces

FROM

[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromQueryURI&appID=&feed=ids%3dga%253AXXXXXXXX%26dimensions%3dga%253ApagePath%252Cga%253Adate%26metrics%3dga%253Abounces%26start-date%3d2015-01-06%26end-date%3d2015-01-20%26max-results%3d50]

Note that the start and end date are in this - so you can simply extract these into your load script and update them to whatever you like when the script runs - here is one example:

let vSince = Date(timestamp(now(1)) - 2);

let vSinceString = date(vSince, 'YYYY-MM-DD');

let vToday = Date(timestamp(now(1)));

let vTodayString = date(vToday, 'YYYY-MM-DD');

let vDateRange = 'start-date%3d$(vSinceString)%26end-date%3d$(vTodayString)';

GoogleAnalyticsConnectorV3_DataFromQueryURI:

LOAD

  dim_pagePath as DataFromQueryURI_dim_pagePath,

  dim_date as DataFromQueryURI_dim_date,

  makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as DataFromQueryURI_date_qv,

  metric_bounces as DataFromQueryURI_metric_bounces

FROM

[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromQueryURI&appID=&feed=ids%3d...]

Taken from an example app on our GitHub page.

This is a common pattern in QVSource - use the connector to generate a 'template' for a particular request, and then simply update the parameters in your load script.


If you have not seen it already, I would recommend reading through this QVSource quick start 'Brain Dump':

QVSource Brain Dump

richard_chilvers
Specialist
Specialist

Hi Abdul

I am just about to try something similar!

The query has something like this to filter a date range:

%26start-date%3d2014-12-25%26end-date%3d2015-01-18%26max-results%3d500]

I was hoping to replace the fixed dates with variables. Have you tried that?

Regards

Not applicable
Author

Hi Chris

The code that I obtained while clicking Run tables/ Generate Script==> in QVSource, when I load it in Qlikview I get Field not found error. I copied the script URL and pasted in browser and I am getting the following:

There appears to have been an error in QVSource. We would recommend that you read the troubleshooting page and other areas of our wiki before requesting support.

Could you please help me on how to rectify it. Do i need to change the URL path or activate some plugin. I want to display the data in qlikview.

Anonymous
Not applicable
Author

Hello,

I've found a way, if you want, you can see here:

http://bit.ly/1PQZ0Wj

Best Regards,