Qlik Community

Qlik Sense Cloud Discussions

Highlighted
piotrama
New Contributor II

How to use a variable in Google Analytics connector in Qlik Cloud

Hi guys,

I'd like to use to the built-in Google Analytics connector in Qlik Cloud to fetch data from numerous Google Analytics accounts (views). I want to have a table of the IDs that I would loop through with the same query. 

Code to get data from a single view is simple and  it's something like this (the number in bold is the view id):

LIB CONNECT TO 'ga (qlikcloud_qlikid_user)';

LOAD metric_sessions as [DataFromQueryURI.metric_sessions];

SELECT metric_sessions
FROM DataFromQueryURI
WITH PROPERTIES (
feed='https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3A123456789&start-date=30daysAgo&end-date=yesterday&metrics=ga%3Asessions'
);

 

I created a table with the list of IDs and tried to do a simple loop over the list and change the ID in bold with the value from the list but I failed. I tried different approaches but nothing worked. Does anyone know how to dynamically change in a loop a part of the With Properties statement?

 

 Code or rather the approach that I tried but failed:

// table with the ids to loop over
web_ids:

Load * Inline [
web_id
123456789
987654321
123123123
];

LET NumRows=NoOfRows('web_ids');

// start the loop
FOR i=0 to $(NumRows) -1

// get the current id from the table

LET vWebId=Peek('web_id',$(i),'web_ids');


LIB CONNECT TO 'ga (qlikcloud_qlikid_user)';

analytics:
LOAD metric_sessions as [DataFromQueryURI.metric_sessions];

SELECT metric_sessions
FROM DataFromQueryURI
WITH PROPERTIES (
feed='https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3A' & $(vWebId) & '&start-date=30daysAgo&end-date=yesterday&metrics=ga%3Asessions'
);
NEXT;

 

Labels (1)