Custom dimension values using "ValueList" or "ValueLoop"

Some times we have to generate user defined dimension values, for example If we want to see list of expressions for Previous and Current year, where Previous and Current year or not direct values from a field.

A straight table that look like below.

                             CurrentYear     PreviousYear    Forecast  Variance
     SumSales
     SumPurchases
     SumBudget
     Expression4

In cases like above where we have to derive custom dimension values, ValueList will be used.

Declaring the dimension:

Valuelist('CurrentYear','PreviousYear','Forecast','Variance')

Defining expressions:

Logic is : Explicitly checking the value in valuelist and defining the expression for that dimension value

Example:
SumSales:
= If(valuelist('CurrentYear','PreviousYear','FCST','Variance') = 'CurrentYear', sum({$<  [Year]={$(vCurrentYear)}>} Sales),

  If(valuelist('CurrentYear','PreviousYear','FCST','Variance') = 'PreviousYear',(sum([Year]={$(vPreviousYear)} >} Sales)),

  If(valuelist('CurrentYear','PreviousYear','FCST','Variance') ='FCST',(sum({$<  [Year]={$(vPreviousYear)} >} Sales))/$(vFCST_Factor)

 If(valuelist('CurrentYear','PreviousYear','FCST','Variance') ='Variance',(sum({$< [Year]={$(vCurrentYear)} >} Sales))/$(vFCST_Factor) )/(sum({$<[Year]={$(vPreviousYear)} >} Sales))-1 ))

  )))

Functions which won't support Alternative states and its work around

Functions which basically operate on current selection state like
GetFieldSelections, GetPossibleCount will not take parameter as Alternative states.

some thing like GetFieldSelections(<State1> Field1) is not possible in Qlikview 11.

Instead we can use general functions like Maxstring, Concat, Count functions.

For example if we want to know selection made in a field which has an alternative state and have 'Allways one selection'.
In this case for accessing the selection made in that field we can use.

maxstring({[State1]} [Field1]).

If allways one selections is disabled and  for getting all selections we can use.

=concat({[State1]} distinct [Field1],'|')

For getting Selection count we can use.

=count({[State1]} distinct [Field1])

Hope it helps :)

Incremental load

Incremental load is basic mechanism used for Optimizing load performance of  the Qlikview applications.

Basic idea is to load data into report in different increments.
Generally in 2 increments-
                                   1. Load newly inserted or updated data from database.
                                   2. Load old data from qvds
Concatenate and generate a new qvd and replace old qvd with new one.

Depending upon the nature of data Incremental loads can be categorized into 3 types.

1. Insert only : New rows will be added into database.
2. Insert and update : New rows , updating  existing rows.
3. Insert Update and delete : New rows , updating and deleting existing rows.

For performing any one of above operations we need ModificationTime field available in database.
And a Primary key is mandatory for performing Update and deletions.

Insert Only:

 

Step1: Filtering newly added records starting from last execution time of the application from database:
 Emp_Table:
SQL SELECT Empid, EmpName, Empage FROM EMP_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(BeginningThisExecTime)#;

Step2: Load old data from qvd and concatenate:
Concatenate
LOAD Empid, EmpName, Empage FROM Emp.QVD;

Step3: Replace old qvd with new one and change Last Execution Time variable:
STORE QV_Table INTO Emp.QVD;
Let LastExecTime = ThisExecTime;

Insert and update:

 

Same as insert except change in Step2.
Where not exists is used to remove duplicate old records.

In first step all records ( newly added and recently updated) will be extracted.

If a record is already in qvd and it got updated it will be extracted in the first step.
This updated record should be deleted from old qvd, for which we use Not exists of Primary key.

Step1: Filtering newly added records starting from last execution time of the application from database:
Emp_Table:
SQL SELECT Empid, EmpName, Empage FROM EMP_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(BeginningThisExecTime)#;

Step2: Load old data from qvd and filter out updated records and concatenate:
LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT Exists(PrimaryKey);

Step3: Replace old qvd with new one and change Last Execution Time variable:
STORE QV_Table INTO Emp.QVD;
Let LastExecTime = ThisExecTime;

Insert Update and delete:

 

It is same as Insert and update with an additional inner join statement before Step3.
For Filtering out deleted records Inner join with (existing) Primary key in database is used.

Step1:
QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(ThisExecTime)#;

Step2:
Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT EXISTS(PrimaryKey);

Additional Step, Inner join to remove deleted records:
Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

Step4:
STORE QV_Table INTO File.QVD;
Let LastExecTime = ThisExecTime;

                                                                    Hope it helps you :).

Using Pick and Match functions for Multiple ifs

This post is about using Pick and Match functions for implementing
Multiple If or Switch case statements.

Consider an example:
We have field City and current GMT( Greenwich Mean time) . and there are 10 different Cities in City field.We need to calculate Current time in each City. So expression will be


If(City = 'New York', GMT-5,If(City = 'London', GMT,If(City = 'Delhi', GMT+5.30,If(City = 'San Francisco', GMT-8...)))) as CurrentTime

Or in User-Interface (UI)

CurrentTime = If(City = 'New York', GMT-5,If(City = 'London', GMT,If(City = 'Delhi', GMT+5.30,If(City = 'San Francisco', GMT-8...))))

Multiple Ifs are CPU and Memory intensive operation.
We can improve its performance using below expression.

CurrentTime = Pick( Match ( City, 'New york', 'London', 'Delhi', 'San Francisco', 'Tokyo', 'Tampa', 'Seattle', 'Paris', 'Helsinki',')
,GMT- 5,GMT,GMT+ 5.30,GMT-8,GMT+9,GMT-5,GMT-8,GMT+1,GMT+2)

Match:
Match(FieldName,'Fieldvalue1','Fieldvalue2' ) : Returns position of matching Fieldvalue.
Match(City,'New york','Delhi') : will return 1 when City is New York and 2 when City is Delhi.

Pick:
Pick(Index,Expression1,Expression2) : Returns result of Expression which Position= Index.
Pick(1,2+3,2+2) returns 5.

In expression above Match function will return Index corresponds to City.
Index =Match(City,'Newyork','London','Delhi','California','Tokyo','Tampa','Seattle','Paris','Helsinki'').

And Pick will return  result of Expression at position = Index
Pick(Index,GMT- 5,GMT,GMT+ 5.30,GMT-8,GMT+9,GMT-5,GMT-8,GMT+1,GMT+2)

Using Trace for debugging

Trace command.

Using trace command we can monitor changing values in the script execution.

Example Code:

for i=1 to 10
Tab:
load
RowNo() as id

AutoGenerate(1);

trace $(i);
next;

When we run report on debugger with next button we can monitor how values in variable i are changing.

This will be very useful when report size is very high and complex to monitor.
using it with Lomited load option will make debugging even more faster.

Alternative states in Qlikview

QV11 introduced this new feature - Alternative states. In QV older versions once a selection is made on a field  same state will be reflected through out the UI.

For example when Countryname USA is selected whole report will highlight information about USA.

Take a example text object showing sum of sales in a country:

• On selecting Countryname as USA text box will show sum of sales in USA.

• On selecting Countyname as UK text box will show sum of sales in UK.

• On selecting both countries it shows sum of sales in both countries.

If we want to compare these two country results we can't do it. ( we can achieve it to some extent by manually having condition in 2 text boxs as Countryname=USA, and UK but this is not dynamic ).


With alternative states we can implement this by creating  different states for different sum's used for comparison. In this case we need to create two states because we need to compare two different sums.
--------------------------------------------------------------------------------------------------------------------------

• To start with , first we should create states in Document Properties -> Alternative states.

Once states has created they will be available throughout the report and can be applied to any sheet object as below.
Select a field in a list box object with state1 as below and 1 more list box with state2.

All sheet objects with a state will have synch among them.
Create  2 text objects with expression sum(Sales) and select State1 and State2.

Below is screen shot of objects that we created.
Objects pointed with arrow are maintaining State1.Including current selection object.
Two text boxes shows sales of 2 different countries.

With this now we can compare sum of sales of any country with any other country.

We can see that Product Name field values are filtered automatically based on selection made in that state( CountyName).
We can also have fields without any states applied(right most). they will be isolated with these objects created for comparison . and they can be used for navigating rest of the report which doesn't have any relation with Alternative states we created.

Link tables

Basic use of Link tables is to solve data modeling issues in qlikview- Synthetic keys and loops.
Main idea behind using linktable is to establish star schema.With Link table at center connecting all other tables.

This post explains basic 3 steps of generating linktables using a example data.
First two steps are common for all data types and 3rd steps requires either joining or concatenation depending upon nature of tables linked.

Consider we have 4 tables in our data model. Sales, Purchases, Product and Country.
Sales table will connect with Product and Country tables with ProductId and CountryCode respectively. and same is the case with Purchases table.

Below is sample data to illustrate: 
---------------------------------------------------------------------------------------------------------------------------
                       Sales table:

                        Purchases table:

                        Product table:

                       Country Table:

                                           
---------------------------------------------------------------------------------------------------------------------------
 When we load above data to qlikview report it's going to form synthetic key table with ProductID and CountryCode fields as below.(If we solve synthetic key problem by using autonumberhash it will give us loop in data model).

Code:
*****************************************************

Sales:
LOAD [SalesTrans ID],
     CountryCode,
     ProductId,
     SumOfSales,
     Date
FROM
..\Linktable.xlsx
(ooxml, embedded labels, table is Sheet1);

Purchases:
LOAD [PurcTrans ID],
     CountryCode,
     ProductId,
     Date
FROM
..\Linktable.xlsx
(ooxml, embedded labels, table is Sheet2);

Product:
LOAD ProductId,
     ProductName
FROM
..\Linktable.xlsx
(ooxml, embedded labels, table is Sheet3);

Country:
LOAD CountryCode,
     CountryName
FROM
..\Linktable.xlsx
(ooxml, embedded labels, table is Sheet4); 

*****************************************************

Steps to generate link table:

Step1: Make sure all tables which has to be connected with link table will have  primary key.This key is used to connect with linktable.

( In our example all tables have primary keys , if primary key is not present generate them using RowNo() or RecNo() etc. )

Step2: Rename all foreign keys in relevant tables( which are causing synthetic keys). By this associations with dimension tables will be removed. Rename Productkey to O_ProductKey and CountryCode to O_CountryCode. This fields can be drooped at end of the code as they will be loaded into link table in below step. More over if present they form synthetic key.

Step3: Generate Link table: depending on nature of data, we need to do joins and/or concatenations to generate link tables. here as sales   and purchase tables will not have any relationship among them we can concatenate data of these 2 tables to generate Linktable. Load Primarykey and foreign key of Sales table and concatenate it with loading  Primarykey and foreign key of Purchase table.

Code:

 *****************************************************

Sales:
LOAD [SalesTrans ID],
     CountryCode as o_CountryCode,
     ProductId as o_Productid,
     SumOfSales,
     Date as SalesDate
FROM
..\Linktable.xlsx
(ooxml, embedded labels, table is Sheet1);

Purchases:
LOAD [PurcTrans ID],
     CountryCode as o_CountryCode,
     ProductId as o_Productid,
     Date as PurchaseDate
FROM
..\Linktable.xlsx
(ooxml, embedded labels, table is Sheet2);

Product:
LOAD ProductId,
     ProductName
FROM
..\Linktable.xlsx
(ooxml, embedded labels, table is Sheet3);

Country:
LOAD CountryCode,
     CountryName
FROM
..\Linktable.xlsx
(ooxml, embedded labels, table is Sheet4);

/************************/
Linktable:
load
[PurcTrans ID],
o_CountryCode as CountryCode

Resident Purchases;

Concatenate(Linktable)
LOAD [SalesTrans ID],
       o_CountryCode as CountryCode,
      o_Productid as ProductId
Resident Sales;

/************************/

Drop fields o_CountryCode,o_Productid; 

  Resultant Data model :

  Hope it helps you. :))