Qlikview : Multiple Schedules for Qlikview

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.

Workarounds:

  1. Setup jobs in windows scheduler to reload QVW documents
  2. 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;

Exit Script;

else

LET vLastRun='Ran without any time restrictions';
LET vForceRun = 0;

end if;

A New Choice of Armor: The Flag Matrix

iQlik - Everything QlikView.

As you know already, I’m a big fan of using Set Analysis in my QlikView applications, especially for Point In Time Reporting. However, there might be some specific situations in which Set Analysis just won’t cut it and you need an alternative.

In Context

I found myself in that situation this month in a project where the QlikView application was required to have Sales Data to the highest detail (resulting in a Fact Table of about 300 million rows for two years worth of data) and several analyses of the Pint-In-Time kind were to be made. So, I started doing the usual stuff: built the Data Model, prepared the calendar with some fields to optimize the use of Set expressions, and started creating the objects. To my surprise, when I created the PITR analysis objects, I noticed a poor performance, a very poor performance indeed. I say “to my…

View original post 848 more words

Qlikview : Generate a running total column in load script

Create a simple view with Sales for each year.

SQLScript

yearlySales

Here is the qlikview script to load data from the above view and create a new column for running total.

QVWScript

Create a chart to show the Sales and Cumulative Sales against Year.

Chart

Here is the chart without any filters:

AllSales  .

Here is the chart after selecting years from 2013-2016.

YearSelection

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:

YearlySales:
LOAD
YearNo,
Sales,
RangeSum(Sales, Peek('CumSales')) AS CumSales;
LOAD    *;
SQL    SELECT    *
FROM     $(vSQLDB).dbo.YearlySales;

Qlikview : Load from QVD Files

You can load from QVD files in three methods as shown in the screen shot below:

LoadFromQVD

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.

LOAD *
FROM F:\Qlikview\Data\QVD\Items.QVD(qvd);

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.

LOAD *
FROM ..\..\..\Data\QVD\Items.QVD(qvd);

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\';

LOAD *
FROM $(vQVDFolder)\Items.QVD(qvd);

Qlikview : Load from Excel File

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.

LoadFromExcel

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.

Note:
Here is the code from the script if you want to copy and paste.

Let vConfigFilesFolder = '..\..\Config Files\';
GLAnalysis:
Load
"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"
FROM
[$(vConfigFilesFolder)GL Analysis Codes.xlsx]
(ooxml, embedded labels, table is [GL Analysis]);

Qlikview : Incremental Loading

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:
IncrementalLoad

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.)

Note:

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;
END IF


GLEntry:
LOAD *;
SQL SELECT EntryNo,
GLAccountNo,
PostingDate,
DocumentType,
DocumentNo,
Description,
Amount
FROM GLEntry
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');
END IF

// 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);
END IF

STORE GLEntry INTO '..\Data\GLEntry.qvd';
DROP TABLE GLEntry;