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

[#1]

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,

[chrisbrain]

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]

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

[#4]

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]

Hello,

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

http://bit.ly/1PQZ0Wj

Best Regards,