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

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

Mon, 19 Jan 2015 09:34:30 GMT

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,

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

chrisbrain — Tue, 20 Jan 2015 11:59:43 GMT

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%3dga%253AXXXXXX%26dimensions%3dga%253ApagePath%252Cga%253Adate%26metrics%3dga%253Abounces%26$(vDateRange)%26max-results%3d50]

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

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

richard_chilvers — Tue, 20 Jan 2015 12:13:21 GMT

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

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

Tue, 14 Apr 2015 11:14:33 GMT

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.

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

Anonymous — Wed, 17 Feb 2016 18:35:01 GMT

Hello,

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

http://bit.ly/1PQZ0Wj

Best Regards,