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)