Copy Qlikview bookmarks from one server to another

We are currently in the process of moving applications from one server to another. I took this opportunity to do a clean install on the new server and upgrade Qlikview from version 12.10 to Qlikview Nov 2018 version (12.30).

The main issue is I had to port the bookmarks from the old server to the new one. In previous versions the easiest option would be to copy the .shared files over from the old server to the new one.

However, 12.10 had .shared extension and Nov 2018 version have .TShared extension. The file format is different, so direct copy and paste won’t work.

Here is the solution:

1. Copy the .Shared file over to the new server.
2. Convert the .Shared file to .TShared file using QVS.exe in command prompt.

   C:\Program Files\QlikView\Server>QVS.exe -x "C:\Qlikview Source Documents\ExampleFile.QVW.Shared" -p -f tx

3. This will generate .TShared_clean file.

   e.g.ExampleFile.QVW.TShared_clean

4. Rename the file to .TShared and place it with the .qvw file.
5. This should create the bookmarks in the new server

 

 

 

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.

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:

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


Qlikview : Load from another qlikview document (Binary Load)

A qlikview document can be binary loaded into another qlikview document as below:

BINARY [TestDocument.qvw];

Qlikview : Load from QVD Files

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.

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.

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:

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;


Qlikview : Concatenate Tables

If you load two tables that have some common columns then it will create synthetic keys during the load which should be avoided in Qlikview. There are two ways to eliminate the synthetic keys. One of them is to concatenate both tables.

 

When there are two tables have few common columns you can concatenate them so that the rows from the second table will be appended to the first one.

In most cases first table and the second may have some columns that do not exist in the other. In this case Qlikview will fill the missing columns with null values. You can avoid this behaviour by passing empty strings to the missing fields during the load.

Also include a new column to indicate the original table it belongs to.

Example:

GLEntry table has the following columns:
EntryNo,
PostingDate,
DocumentNo,
Description,
AccountNo,
Amount

BudgetEntry table has the following tables:
EntryNo,
PostingDate,
AccountNo,
Amount

As you can see above both GLEntry and BudgetEntry tables have few common columns but BudgetEntry does not have DocumentNo and Description columns.

So we can concatenate these two tables to avoid synthetic keys and reduce the number of tables in qlikview.