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

Qlikview and Facebook integration

Integrating your Facebook profile into Qlikview

This blog will explain how to implement your Facebook analytics into Qlikview using  QVSource Facebook connecter.

Steps in installing QVsource:

1. You can download one month free trail virsion of QVsource at http://www.qvsource.com/wiki/.
2. Once you are able to install and run QVSource into your system it will list out various connectors available.

 

3. Double click on Facebook personal connector( for getting tables and data related to your personal Facebook account.  

4.Click on authenticate for authenticating with your Facebook account.

5  5. Once authentication is done we should be able to view tables and it’s data from “Connector Data” tab.

    

     6.Load script for extracting this table data will be “Qlikview Load Script”

  

       Sample KPIs that we can implement with above tables:

•  Month wise count of birthdays
• Top 10 Locations having highest number of friends.
• Gender pie chart distribution
• Filtering using age group and home town
• Searching based on name and display of birthdays.

• Deeper analytics:

•  Page likes made by friends 
• Trend analysis for friends page likes and total page Likes for that page in FB

• Likes by gender and category 
• Total likes made by each friend

• All Status updates made by friends and basic status analytic:

  

 Hope it helps you.:))