Qlik Web Connectors may generate a load statement including variables, such as dates, which need to be hardcoded.
Environment:
Qlik Sense Enterprise on Windows
QlikView
In this article we will explain how to construct dynamic load URLs in QlikView and Qlik Sense to handle parameters like dates using some scripting techniques.
In this example, we'll look at a Google Analytics query looking at visitors and page views over time. In Qlik Web Connectors UI we've entered a start and end date and end up with the following load script to put into our QlikView or Qlik Sense application:
1
2
3
4
5
6
7
8
9
10
11 |
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
| 1 |
...&startDate=01-01-2015&endDate=31-01-2015... |
In our application, we can define two QlikView/Qlik Sense variables such as my start date and myEndDate and then use these to replace the hardcoded values.
1
2
3
4
5
6
7
8
9
10 |
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/Qlik Sense 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:
1
2 |
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14 |
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 the flexibility of the QlikView/Qlik Sense 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.
.png "Former Employee"){kind=icon}
