Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Filtering Google Analytics data in load script

Hi all,

I need to filter data from different Google Analytics data connections in my load script.

I have seen examples of filtering using "where" SQL statement but I've always got error when tried.

For example, how could I edit this script:

GARsummaryM:

LOAD

    dim_yearMonth,

    metric_newUsers

FROM [lib://GAR-summary-mensual (escorpesqs_divisadero)]

(qvx);

to get only data matching dim_yearMonth = '201701' or metric_newUsers > 1000?

Regards,

Suni

4 Replies
Anonymous
Not applicable
Author

Hello,

Did you try with:

GARsummaryM:

LOAD

    dim_yearMonth,

    metric_newUsers

FROM [lib://GAR-summary-mensual (escorpesqs_divisadero)] (qvx)

WHERE dim_yearMonth = '201701' or metric_newUsers > 1000 ;

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Matias,

The issue with that approach is all of the data will need to be served up by Google to then be ignored by the query.  With a single dimension at a monthly granularity this probably wouldn't be a problem - but if you had a different query it would be a nightmare.

To do this properly the limitation needs to go in the URL that is passed to Google.  This could be done in the connection, but it would not be possible to change it from one query to the next.

The only other approach is to disable "Standard Mode" which will allow you to modify the parameters of the query on the fly - but this is a security risk.

There is more on standard mode usage here: Load Web File dynamically

There are other places where this is referenced also on the Community.

Steve

Anonymous
Not applicable
Author

Hi Matias,

It simply works!

I wasn't able to locate the WHERE clause in the right place .

Many thanks,

Anonymous
Not applicable
Author

Hi Matias,

I understand the point, unfortunately, IT team doesn't approve Standard Mode disabling so I have to perform a huge amount of request and it is going to be very painful to update data when needed.

You can directly filter data in the Analytics request through "filter" parameter but a limitation of characters exists so anyway, I needed to be capable of using filtering inside the load script.

Kind regards,

Asuncion