Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)

Often, QVSource will generate a load statement for you which includes variables, such as dates, but this variable is hardcoded.

So, this page explains how to construct dynamic load URLs in QlikView and Qlik Sense to handle parameters like dates using some scripting techniques. Where we mention QlikView, we also include Qlik Sense.

If you are a Qlik Sense user, then please make sure that you read this page first, because you need to put Qlik Sense into a legacy mode in order to work with arbitrary load URLs.

In this example, we'll look at a Google Analytics query looking at visitors and page views over time. In QVSource's UI we've entered a start and end date and end up with the following load script to put into our QlikView application:


GoogleAnalyticsConnectorV3_DataFromTemplateQuery:
LOAD
	metric_visits as DataFromTemplateQuery_metric_visits,
	metric_pageviews as DataFromTemplateQuery_metric_pageviews
FROM
[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromTemplateQuery&appID=&profileId=11111111&
		prebuiltQuery=metrics%3dga%253avisits%252cga%253apageviews&startDate=01-01-2015&endDate=31-01-2015]
(qvx);
// IMPORTANT: If, when loading the above script in QlikView, you receive a QlikView 'Script Error' Dialog box with a 'Field Not Found'
// (or other) error, the first thing you should do is copy and paste the full request URL (i.e. the URL between the square [...]
// brackets IN THE QLIKVIEW / QLIK SENSE SCRIPT ERROR DIALOG) into a browser address bar and check the response.

Focusing on the date parameters we have


...&startDate=01-01-2015&endDate=31-01-2015...

In our application we can define to two QlikView variables such as myStartDate and myEndDate and then use these to replace the hardcoded values.


let myStartDate = '01-01-2015'; //date format is DD-MM-YYYY
let myEndDate = '31-01-2015';

GoogleAnalyticsConnectorV3_DataFromTemplateQuery:
LOAD
	//the fields
FROM
[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromTemplateQuery&appID=&profileId=11111111&
		prebuiltQuery=metrics%3dga%253avisits%252cga%253apageviews&startDate=$(myStartDate)&endDate=$(myEndDate)]
(qvx);

So, now we've got a table that will load based on whatever the two variable values are.

The next step is to make these variables dynamic. We'd like the start date to be 7 days ago and the end date to be yesterday, so a 1 week period.

Use the Today() function in QlikView to return today's date and then subtract the number of days we want. Then, using the Date() function we format the date to what we need:


let myStartDate = Date(Today() - 7,'DD-MM-YYYY') ; //date format is DD-MM-YYYY
let myEndDate =  Date(Today() - 1,'DD-MM-YYYY') ;

We end up with the following script which returns values for the previous 7 days.


let myStartDate = Date(Today() - 7,'DD-MM-YYYY') ; //date format is DD-MM-YYYY
let myEndDate =  Date(Today() - 1,'DD-MM-YYYY') ;
     
GoogleAnalyticsConnectorV3_DataFromTemplateQuery:
LOAD
	metric_visits as DataFromTemplateQuery_metric_visits,
	metric_pageviews as DataFromTemplateQuery_metric_pageviews
FROM
[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromTemplateQuery&appID=&profileId=11111111&
		prebuiltQuery=metrics%3dga%253avisits%252cga%253apageviews&startDate=$(myStartDate)&endDate=$(myEndDate)]
(qvx);
// IMPORTANT: If, when loading the above script in QlikView, you receive a QlikView 'Script Error' Dialog box with a 'Field Not Found'
// (or other) error, the first thing you should do is copy and paste the full request URL (i.e. the URL between the square [...]
// brackets IN THE QLIKVIEW / QLIK SENSE SCRIPT ERROR DIALOG) into a browser address bar and check the response.

This is one way of creating dynamic dates in URLs and there are probably other ways of doing this, such is flexibility of QlikView script.

You might need more elaborate date calculations such as the whole of the previous month, which of course needs to correctly reflect the number of days in that month.

We'd recommend that you search Qlik Community for example script for these other scenarios and if you can't find what you need, just post a question on there.


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd