In Qlikview server, you can have only one schedule for a Qlikview document. I have quite a few QVD generator qlikview documents that need to be loaded every 30 minutes during the business hours and do not need to load during the non-business hours.
This is not possible with scheduling the reloads.
- Setup jobs in windows scheduler to reload QVW documents
- Setup jobs in Qlikview server to run every 30 minutes but add the following code at the beginning of the script to decide whether to exit the script or reload based on the time.
if match(hour(now()),19,20,21,22,23,0,1,2,3,4,5) and (vForceRun = 0) then
LET vLastRun='Did not load due to the Time Restriction';
LET vForceRun = 0;
LET vLastRun='Ran without any time restrictions';
LET vForceRun = 0;
Create a simple view with Sales for each year.
Here is the qlikview script to load data from the above view and create a new column for running total.
Create a chart to show the Sales and Cumulative Sales against Year.
Here is the chart without any filters:
Here is the chart after selecting years from 2013-2016.
There are various other methods to get the running total but most of them do not work when a year selection is made. Those methods do not give the correct starting totals.
Here is the qlikview script if you want to copy the code:
RangeSum(Sales, Peek('CumSales')) AS CumSales;
SQL SELECT *
A qlikview document can be binary loaded into another qlikview document as below:
You can load from QVD files in three methods as shown in the screen shot below:
Method 1 : Specifying the physical location of the QVD file
This method is not recommended as if you move the Qlikview project folder then you will need to edit the script to specify the new location.
Method 2 : Specifying the reference location of the QVD file
Instead of specifying the physical location of the qvd file as in Method 1, you can specify the path with reference to the location of the report. So moving the project folder does not require any changes to the script. However, if you change the directory structure of the Qlikview project then you will need to update the QVD data folder in each LOAD from QVD statements.
Method 3 : Specifying the reference location in a variable
In this method, you need to store the reference location of the QVD files in a variable and use the variable in the load statements. By this way you need to update the variable if you ever want to change the directory structure of Qlikview Project.
LET vQVDFolder = '..\..\..\Data\QVD\';
Here is an example to load data from an excel file in Qlikview. I have specified the location of the excel document in a variable and using that variable in the load statement.
In the above script ooxml stands for open office xml and ’embedded labels’ key word indicates the first row has the column names and the spread sheet to load data from the document is specified after “table is” keyword.
Here is the code from the script if you want to copy and paste.
Let vConfigFilesFolder = '..\..\Config Files\';
"No." as "Account No",
[GL Analysis Code 1] as "G_L Analysis Code1",
[GL Analysis Code 2] as "G_L Analysis Code2",
[GL Analysis Code 3] as "G_L Analysis Code3",
[GL Analysis Code 4] as "G_L Analysis Code4"
[$(vConfigFilesFolder)GL Analysis Codes.xlsx]
(ooxml, embedded labels, table is [GL Analysis]);
When you have a have large data warehouse it is not a good idea to load all the data every time as it can take a long time to load. You can incrementally load only the new records to tables where the old data does not change (often).
Here is an example:
After writing the above code in the script editor then create a variable vLastGLEntry in Settings –> Variable Overview. Assign value 0 to it.
Then create an input box add variable vLastGLEntry.
This will allow the user to see the finally loaded entry value as well as allow the user to reset the value so all the records will be loaded. (Before resetting to zero, you must ensure that the qvd file is deleted.)
Here is the code from the above screen shot:
// Declare and initialise a variable to hold the last entry
IF ISNULL($(vLastGLEntry)) THEN
SET vLastGLEntry = 0;
SQL SELECT EntryNo,
WHERE EntryNo > $(vLastGLEntry)
ORDER BY EntryNo;
// Update the vLastGLEntry variable
IF PEEK('Entry No_',-1,'GLEntry') > 0 THEN
LET vLastGLEntry = PEEK('Entry No_',-1,'GLEntry');
// Check the existence of the qvd file and append the records from the qvd file
IF NOT ISNULL(QvdCreateTime('..\Data\GLEntry.qvd')) THEN
Concatenate(GLEntry) LOAD * FROM '..\Data\GLEntry.qvd' (qvd);
STORE GLEntry INTO '..\Data\GLEntry.qvd';
DROP TABLE GLEntry;