Improve Dexterity Of Data Visualization With QlikView Training In Hyderabad

In the arena of Data Science and Analytics, data visualization is integral for businesses and corporates. Now, businesses are in need of professionals with proficiency in data visualization and adept in communicating the same. QlikView is one of the best new generation data visualization and Business Intelligence software. It provides a platform of transforming data to vital information. Across the globe, many noted organizations now rely on this tool to consolidate, visually analyze and search data to get insight for businesses. The simplicity of this instrument has lured companies to opt for it. To gain business insights with this software you can rely on us,  for QlikView training in Hyderabad.

Improve decision-making process

Decision making of enterprises depends on the accessibility of accurate information. Adequate knowledge of this software helps professionals to make fast queries without dealing with the different hierarchies and structure as with various tools. You can deploy it quick and easy to learn. In case, you want to improve the dexterity of data visualization then choose us, one of the best in this arena. We have a systematic and structured approach to training that aims at providing adequate knowledge to professionals. With our training, analysis of data and obtaining insight into business will be easy.

Expected outcome of QlikView training 

The aim of this training is to improve business analytics. The curriculum of training programs comprises of fundamentals and advanced topics. Over the years, we have turned out to be one of the best pioneer QlikView training institutes in Hyderabad. With this training, few outcomes that you can expect are

  • Participants will gain real-time application of this software through different projects
  • Hands on experience of dealing with data modeling
  • You will gain knowledge of chart level and script level functions
  • Gain an insight of design techniques and different shortcuts that will help you to take less time in area of work
  • Dexterity to publish documents on server and access it

Prerequisites for enrolling

Only authorized training institutes can conduct these courses. You can opt for classroom or online training programs depending on your need. Trainers are skilled, have practical experience and are a specialist in this arena. You can pursue this training with basic knowledge level in SQL. Designers, developers, and Power end-users can opt for this training program. This will help professionals dealing with data visualization. Both the pros and novices can enroll this training program.

Emphasis area of curriculum

It is job-oriented course, and we aim at providing placement assistance to different organizations. What makes us stand apart from others emphasize on hands-on training and various demonstrations by experts. Designer of the curriculum have profound experience in this arena, and the curriculum aims at taking a student to a next level altogether. Focus of this QlikView training program is on

  • The design of user interface and for this, experts impart knowledge on best practices, design, layout, and reports.
  • Participants will learn the methods of modifying and adding objects like gauges, multidimensional tables, and charts.
  • You will receive a comprehensive knowledge of the software and its uses.

A glimpse of contents

Duration of training depends on the course that you have opted. The curriculum of our training course comprises of both basic and advanced topic. Whether you choose classroom or QlikView online training, there is scope for both didactic and theoretical classes. Irrespective of the variant that you choose, topics covered are

  • Basics of data warehousing
  • Introduction of QlickView
  • Data loading with QlickView
  • Scripting and functions
  • Data structure
  • Design Concepts
  • Different sheet objects- table box, list box, bar chart in QuickView
  • Server Components
  • Security Overview

A comprehensive approach

In these courses, trainers assess participants at regular intervals. Through different innovative methods, they aim at a continuous improvement of participants. The comprehensive approach of our training programs provides leverage to professionals and help them to perform well in respective fields. One-to-one interaction between our experts and participants is an important aspect of our training program. Our expert will help you with real time scenarios, examples and sample data that will help you to have an insight of this project. In different projects that you undertake, our experts will provide you with assistance throughout the life cycle.

Important features of online classes

Well-designed manuals, practice exercises, in-depth study materials and laboratories help participants to learn fast. If you have a time constraint, you can even opt for QlikView online training in Hyderabad. Flexible timing of these sessions has proved to be beneficial for professionals. Few important features of our training programs are

  • You can opt for different demo classes depending on your convenience.
  • Every session can be recorded, so that you can listen to it or refer as and when needed.
  • There is a provision of fast track, normal track and weekend online training programs.

Get leverage with this course

The emphasis of this training program is to improve the dexterity of professionals. In our training program, we do not teach you only the methods of using the software. We help participants in the implementation of this knowledge in real time scenario and skill in dealing with customers. With our course, you can stay abreast of the industry guidelines and cater solutions in professional sphere based on the expectation. Apart from catering detailed information regarding data visualization and use of this software, we also help our participants in seeking employment in several corporates. Our experts will help you in preparing a resume and guide you in answering different interview questions.

Get all round guidance

Round the clock technical support by us helps our participants during and after the training program. Our participant can obtain our assistance even after completion of the training program. As per the recent trends with QuickView training there are high chances of obtaining a lucrative job. With our training, professionals can expect to cater a comprehensive solution in the arena of business intelligence.This software is a perfect replacement for different reports that often results in delays. Now, instead of using different reports and dashboards, you can use this software for fast and appropriate decision-making. To get a competitive edge in professional sphere enroll to our training program immediately.


QlikView ClassRoom Notes

History of QlikView:-

Qlikview of is the combination of ETL and reporting tool is introduced by 1993

Company:  quik TECM

ETL – Extract transform load

Qlik  means……




K- Knowledge


  •  Present version of Qlikview is 11.2 SR9
  • The company name also changing Qlik tech. in changing the year is 1996.
  •  Trail version of QlikView is the personal edition.
  • Licensed version of QlikView is desktop edition
  •  Qlikview is the Business intelligence tool

Existing tools:-

  RT                                     ETL

Cognos                            Informatics

Spot fire                            MSBI

Micro strategy

SAP_-BI                           Data stage


SSAS                                SAP-BO


QlikView files & servers:-


QVW  –  Qlikview work file

QVD    –   QlikView data file

QVX    –  QlikView exchange file

QVB    – QlikView batch

QVP    – Qlikview publisher

QVS    –   Qlikview server

QDS   –  QlikView distribution server

QVAP  –  Qlikview access point

QVMC  –  QlikView management console


Online process  – immediate process

Batch process    – scheduling process.


Qlikview architecture:-


1.    Qlikview desktop   –         developer is  work on

2.    Qlikview publisher  –        administrator

3.    Qlikview server     –          administrator

4.    Qlikview access point  –    business user


QlikView desktop:-

1.    Connecting to server

2.    Fetching data from server

3.    Transformation of data- functional keys

4.    Data model creation – synthetic keys, Circular reference


Qlikview Publisher:-

1.    Reloading (or) refreshing of data

QVB –       scheduling

QVB –       Triggering

we mostly go for scheduling


2.    Distribution of data:

a. one QlikView document into multiple QlikView

b. Document as per the user rights.

3.    Sending mails  – sending of mails, reloading & distribution of data will do by a publisher.


Qlikview server:-

1)    In- memory analytics

2)    Client-server communication

3)    QlikView management control – developer login  to the server

4)    Qlikview access point – client will be login into the server.

5)    Reading / writing to ACL –  [Access client link ]


Architecture overview:-

Types of keys:-

Primary key: – by using the primary key directly fetch the information.

Foreign key: – same record from primary key number of times

Composite key: – a combination of two or three fields to bring the information.

Perfect key:- unique value including null values



Types of tables:-

1.    Dimensional table: – it contains one primary key and relevant attributes to maintain business history

2.    Fact table:-

It contains set of foreign keys W.R.T to all the dimensions tables associated to it and fact information

Facts table are called as measures or numerical information

Fact and dimension tables will be interacted with help of primary key and foreign key relationship


[sociallocker] Dimensional model:-

1.    Star schema

2.    Snowflake schema

Star schema: – the schema in which all the dimension table are directly connected with fact table is called as star schema


Snowflake schema:-

The schema in which all the dimensions tables are not directly connected with fact table is called as snowflake schema.

Normalized table: – Normalized table means separation

Denoralized table: – Denoralized table means merging


Types of dimensional tables:-

1.    Confirmed dimension table:-

If the dimension is shared by multiple fact tables then the dimension is called confirmed dimension table


Degenerate dimension:-

It is an attribute in the fact table and used to satisfy the latest requirement from the customer.

Junk dimension:-

The dimension which contains textual codes [abbrerations] and flags variables is called junk dimension.

Large dimension:-

If the dimension contains number of columns [>100] or number of rows [>2 million} then it is called as the large dimension.


Mini dimension:-

If he dimension contains less number of column [<5] and less number of rows [<100] then it is called as the minimum dimension.


Slow changing dimension:- [SCD]


The dimension which changes W.R.T number of rows or number of columns over a period of time is called as slowly changing dimension


Types of SCD:-

Type -0 . it maintains data only

Type -1 . it maintains complete data about all transactions

Type -2. it maintains current data and pervious


Static dimension:-

If the dimension is loaded with data only once in a project life cycle time and will never change then, it is called as the static dimension.


Aggregate dimension:-

The dimension that is loaded at a base level and it possible to calculate aggregate functions like sum(), Avg (), min(), etc.  is called as the aggregate dimension.

Multiple-valued dimension:-

If the dimension attributes contain many to many relationships within the data, then the dimension is called as the multi-valued dimension.


Types of fact tables:-

1.    Snapshot fact table

2.    Cumulative fact table

3.    Factless fact table


o    Snapshot fact table: – it contains information at a specific point of time

o    Cumulative fact table: – it contains information about a period

o    Factless fact table:- the fact table which doesn’t contain any fact information is called as factless fact table


Shortcuts of QlikView:-

  • Ctrl + E  – edit script
  • Ctrl +  R  – Reload
  • Ctrl +  shift + R  – partial load
  • Ctr + T  — tabular view
  • Ctrl + s  – save
  • Ctrl + z – undo
  • Ctrl  + Alt + V  – variable overview window


Types of loads in QlikView:-

1.    Loading from Excel, txt file

2.    Loading through ODBC, OLEDB connection

3.    Resident load

4.    Inline load

5.    Mapping load

6.    Auto generate load

7.    Info load

8.    Bundle info load

9.    Cross table load

10.    Incremental load

11.    Partial load

12.    Preceding load

13.    Hierarchy load

14.    Semantic load


Table format:-

Txt  – delimited text file  .txt

Fix    – for a fixed record length file

Dif  – for a .dif [data interchange file]

Biff – QlikView can interpret data in the excel files using the diff format [binary interchange file format]

OOXML – QlikView can interpret data in the Excel 2007 OOXML format.

Html  – if a table is part of an HTML page file, HTML should be used.

Qvd – the format DVD is the proprietary

Qvx  – qvx is a file / stream format

Alternative key: – by using AK the primary key will be fetcher



Operators in QlikView:-

1.    Arithmetic operators :- [ +,-./,*] . mod().

2.    String operators:-

8- string concatenation [ ‘qlik’ & ‘view’] concatenation means adding at te end


Like  -for the string comparison.

*, ?  – for wildcard characters

*      – any value and for any non of characters

?     – for any value and for single characters.



Qlik like  ‘ Qli*’   – –  1

Qlik like ‘Q*’      –  –  1

Qlik like  ‘*’         – –   1

Qlik like  ‘Q?ik’         – –   1

Qlik like ‘ q?lik’      — 0

Qlik like  ‘Qlik?      –1


Logical operators:-


1.     AND

2.    OR

3.    NOT

4.    XOR

A    B       A & B        A or  B                       A xor B

0    0         0                  0                               0

0    1         0                  1                               1

1    0         0                  1                               1

1    1          1                 1                               0

Relational operators:-

•    >    . greater than

•    <   . less than

•     >= . greater than equal to

•    <= . less than equal to

•      =  . equal

•    <>  . not equal to


Bitwise Operators:





>>(Right Shift)

<<(Left Shift)

Ex:   5 BITAND 3 (BIT AND Eg)





Mathematical Constants functions in Qlik View:

e ( )     –      2.71

pi()    –       3.14

rand()    –        0 to 1

true()     –         -1

false()   –          0


Counter Functions:




Auto Number()

Null Functions:

Null ()

isNull()  – To Check it is null or not.

Logical Functions:

Isnum() – To Check number or not

Istext() – To Check Alphabet or not

is partial reload()

Short Cut Key – Ctrl + Shift +R


*For the Remaining purpose we use 3 keywords.



*we have to rename the fields within the load we use ‘as’

*If we have to type the date in the field Day & ‘/’ & month & ‘/’& year as Date.

* If we have ‘Spaces’ in between the String we have to put “Square brackets at starting and end of the Strings”

* All this will be done within the Load.



LOAD  [ Transaction ID]




Day  & ‘/’ &  Month & ‘/’  & year as Date.

[Salesman ID]

[Product ID]

[Serial NO]

[ID Customer]

[List Price]


(10/100)* Sales as Bonus,

Sales + (10/100) * Sales as total sales,

[Grass Margin]

*At the end of the field we have to put the comma, Suppose, if the Bonus is 100 as fixed, then 100 as Bonus Sales + 100 as total Sales.

LOAD Rec No( ) as Recno,

[Transaction ID]




[Salesman ID]

[Product ID]

[Serial NO]

[ID Customer]

[List Price]

Sales / 100 as Sales,

Round (Sales /100) as Round Sales,

Ceil( Sales/100) as Ceil Sales,

Floor (Sales/100) as floor Sales,

100 as Bonus,

Sales + 100 as total sales,

[Gross Margin]

•    If we have to round off the values as 0.5 above is 1,0.5 below is 0 as Round (Sales/100)

•    If we have to get values of above

Example: 6.1 – It will get 7 for this we use ceil.

•    If we have to get between values of low

Example: 6.9 – It will get 6 only for this we use the floor.


1. For Single Line Comments   //

2. Multiple Line Comments   /* Hi

Hello  */

3. Purpose of Comments    Understandability


SQL Select * from Table1;

SQL Select field 1, field 2 —— from Table1;

LOAD * from file1.csv;

LOAD year, month, sales from file2.txt;

Alias is used to rename before the load Statement

Rename fields Year to Year,

Rename field name to which name we have to change


//Load * from Transact.CSV;

Load year,

Month as month,

‘Q’ @ Ceil (Month/3) as Quarter,

‘Q’ & Ceil (Month/3) & ‘  ‘ & Year as Quarter Year,

Sales from Transact.CSV;

In Qualify, all fields are renamed

In Unqualified except  that field remaining fields are renamed.

Qualify *;

UnQualify Year;


//Load * from Transact.CSV;

Load year,

Month as month,

‘Q’ & Ceil (Month/3) as Quarter,

‘Q & Ceil (Month/3) & ‘ ‘ & Year as QuarterYear;

Sales from Transact.CSV;

Rename fields year to year;

Rename Tables Tab to Trans;

UN Qualify:

Rename fields year to year;

Rename Tables Tab to Trans;

•    Resident Load: Load the data from previously loaded table

Resident table:

Load year, Month as month, sales as sales Resident Tab1;

Subset ratio:

The number of keys present in the current table

———————————————————————–   X 100

Total number of key present in the entire data model


Information Density:

The percentage of non-null values is referred by information density.

SubSet-ratio: Ratio of a null of keys present in the current table to the null of keys present in the entire table.

If Single field is common then there is a link between them.

If more than one fields are common between two tables then it makes the synthetic keys.


Disadvantage of Synthetic keys:

  • Due to this wastage of memory.
  • How can we Avoid the synthetic keys

*By remaining the corresponding fields

*By dropping the fields

*By dropping the fields

*By deleting those fields

*By commenting those fields

*By using Qualify and Un Qualify Statements

By using a concept of Concatenation or link table.


What is the difference between Internal table view and Source table View?

*There is one connection between the fields Synthetic keys are not available in source table view and there may be the multiple connections between them.


Filtering the tables at the time of Load Condition.


Resident Table:

LOAD Year, month as month, sales as sales Resident Table name Where Year = 2008;

LOAD Year, month as month, sales as sales Resident Table Name Where Year > = 2008;

LOAD  Year, Month as month, sales as sales Resident table name Where Year >= 2005 and Year <= 2010

LOAD Year, Month as month, Sales as Sales Resident table name Where year> = 2008 and (month =1 or month =3);

LOAD Year, Month as month, sales as sales Resident table name Where (Year > = 2008 and month =1) or month =3 or month =5;

First 100 Load Recno() as Recno, Year, Month as month, sales as sales Resident Tab1;


Rec no is a Previous table is followed . or refers.

Row no is a current table is followed . or  refers.

LOAD Recno() as rec, year, Month as Month, Sales as  Sales Resident Tab1 Where Recno()<=100;

LOAD Recno() as rec, year, Month as month, Sales as Sales Resident Tab1 Where Recno() <=100;

LOAD Recno() as Rec, Rowno() as Row , Year , Month as month, Sales as Sales Resident Tab1 Where Recno()> =30 and RecNo()<=50/

LOAD Recno() as Rec, Rowno() as Row , Year, Month as month, sales as sales Resident Tab1 where mod(RecNo(),2)=0;

LOAD Recno() as Rec, Rowno() as Row , year, Month as month, Sales as Sales Resident Tab1 Where mod(RecNo(),2)<>0;

LOAD Recno() as Rec, Rowno() as Row ,Year, Month as Month, Sales as Sales Resident Tab1 Where even (Recno());

LOAD Recno() as Rec, Rowno() as Row , Year, Month as month, Sales as Sales Resident Tab1 Where odd(Recno());

LOAD Recno() as Rec, Rowno() as Row, year, Month as month sales as sales Resident Tab1 Where mod(Recno(1,5)=0;

Autogenerate Load:

To Create a table automatically Autogenerate table:

LOAD Recno() as Rec Autogenerate 100;

LOAD Recno() as Rec, 100 as Info, Autogenerate 100;

LOAD Recno() as Rec, 100 as Info , Recno() *5 as Mulitiple 5 Auto generate 100;


Chr function converts ASCII value to Character value LOAD Recno() as Rec, Chr (Recno())as Alpha Autogenerate 90 where Recno()>=65;

LOAD Recno() as Rec, Chr (Recno()) as Alpha Autogenerate 122 Where Recno()>=65 and Recno()<=90 or Recno()>=97;

*[an ASCII 97]

*[Z ASCII 90]

*Char Value is converted into ASCII Value we have to use ord()

LOAD Recno() as Rec, Chr (Recno()) as Alpha Auto generate ord(‘Z’) Where Recno()>=ord(‘A’);


LOAD Chr (Recno() + ord(‘A’)-1) as Alphabet autogenerated.

While Clause:

To fetch the same record from n number of times.



LOAD Recno() as Rec, Autogenerate 1 while IterNo()<=10;

*Iter() used along with while clause only.

LOAD Recno() as Rec Autogenerate 2 while IterNo()<=10;

Inline Load:

To Create a table manually.


Inline table:

LOAD * Inline[

ID,        NAME,         SALARY

11,        VIVEK,        XXXX

22,        Venky,        XXXX

33,        Muni,        XXXX

44,        Dinesh,        XXXX];


Order by: To sort out the data


LOAD year, month as month, Sales as Sales resident transact order by year:

LOAD year, month as month, Sales as Sales resident transact order by year asc:

LOAD year, month as month, sales as sales resident transact order by year desc:

LOAD year, month as month, sales as sales resident transact order by year asc, month asc;

LOAD year, month as month, sales as sales resident transact order by year asc, month desc;


Group by: – To aggregation function.

Tab 1:

LOAD year, sum(sales) resident transact group by year:

LOAD year, sum(sales) as total sales resident transact group by year;

*Aggregation “grouping of data”.

LOAD, sum(sales) as total sales,

Max(sales) as Max sales,

Min(sales) as Min sales,

Avg(sales) as Avg sales,

Count(sales) as Count sales,

Resident transact group by year;


DISTANCE: – To remove the duplicated records

LOAD distinct year, Sum (sales) as Total sales,

Max(sales) as Max sales,

Min(sales) as Min sales,

Avg(sales) as Avg sales,

Count(sales) as sales count

Resident transact group by year;

Tab 2:-

LOAD Max(sales) as Max sales, min(sales) as min sales Resident transact;


Chr()        upper()            repeat()

Ord()         lower()            replace()

Len()        capitalite()        evaluate()

Left()        index()            text between()

Right()        trim()            keepchar()

Mid()        rtrim()            subfield()



Settings - variable overview Ctrl +Alt +V

-Set     ->It won’t evaluate the expression

Let     -> It evaluate the expression.

Let and set statement:-

-If we don’t give let, set will automatically default the statement

Eg:- VVal4 = 10* 4+10/3-10; by default tak “Let”

 Set VVal =10;

Let VVal 1 = 10;

    Set VVal 2 = 15;

Let VVal 3 = 15* 3;

    VVal 4 = 10* 4 + 10/3 -10 : By default take “Let”

    Set VVal sales = today();

Let VSales 1 = today();

    Set Vsales 2 = $(Vsales);

Let V sales 3 = Num ($(v sales));

*Char(): to convert ASCII to character value

Let Vchar = char (65); //A

Let Vchar 1 = char(90); //z

Let Vchar2 = char(97); //a

Let Vchar 3 = char(122); //z

Let Vchar 4 = char(48); //o

*Ord(): To convert character value to ASCII value

Let V ord = ord (‘A’); // 65

Let Vord = ord (‘Z’); //90

Let Vord = ord(‘a’); //97

Let Vord = ord(‘z’); //122

Let Vord = ord(‘9’);// 57

*Len(): Length of the given string

Let VLen = Len (‘Qlik view’); 118

Let Vleft = Left (‘Qlik view’,4); //Qlik

Let VRight = Right(‘Qlikview’,4);//View

Let VMid =  Mid(‘Qlikview’,4); // kview

Let vmid1 = Mid(‘Qlikview’4,3);//kvi

Vupper = upper(‘Qlikview’); //QLIKview

Let Vlower = Lower(‘Qlikview’); //Qlikview

Let Vcaptalize = Captalize (hn global solution’); //Hn Global solution.

Let VRepeat = Repeat(‘##’,4);//########

Let VReplace = Replace (‘Qlikview’,’Tech’);//Qliktech

*Trim(): To remove the leading and Trailing spaces

Let Vtrim = Trim(‘Qlikview’); //Qlikview

Let VRTrim = Trim(‘Qlikview’);// Qlikview

Let VLTrim =Trim(‘Qlikview’);// Qlikview



Let VEvaluate = Evaluate(10*3-10/3);

Let VText Between=Text Between(‘<Qlik><view>’,’<’,’>’);//Qlik

Let VText Between=Text Between(‘<Qlik><view>,’<’,’>’2);//View

Let PV substring count = substring count(‘QlikviewQlik tech’, ‘Qlik’);//2

Let Vkeepchar= keepchar(‘dsvjf123456789dbs’,’987654321’);//123456789

Let Vpurgechair =keepchar (‘dsvjf123456789’,’987654321’);//dsvjfdbsv

Let Vsubfield 1 = subfield (‘HN Global solution’, ‘’,1);//Hn

Let Vsubfield 2 = subfield(‘HN Global solution’,’’,2);//Global

Let Vsubfield 3 = subfield(‘HN Global Solution’,’’,3);//solutions.



Tab :

LOAD * Inline L

Name,        Marks,        Stream

Vivek,        100,        ECE

Raja,        99,        EEE

Prasad,        98,        CSE

Surya,        97,        Civil j;



TAB1 :

LOAD  * In line [

Name,        Section,        Branch

Vivek,        A,        ECE

Raja,        A,        EEE

Prasad,        B,        CSE

Surya,         B,        Civil   ];



Types of Joins:-

1.INNER Join

2. Outer Join

3. Left Join

4. Right Join

1. OUTER JOIN: -By default taking the Qlikview is outer Join

2. INNER Join: – Common fields in two tables that files only display in the inner join.

3. LEFT Join: – Only first table ID’s with common fields display

4. RIGHT Join: – Only second table ID’s with common fields display.



Types of Keeps:-

1.    Inner keep: – Common values present in two different tables.

2.    Left Keep: – First table performed and Table 18 only common values

3.    Right Keep: – the Second Table presented and Table 18 only common values.


*CONCATENATION: – Appending data to the previous or given table, appending means adding data

at the end of the table.

Types of Concatenation:-

1.    Auto Concatenation:-

Auto concatenation will be performed if all fields are common.

2.    Forced Concatenation:- Concatenate (tab) Logic forced concatenation data even fields are common.

3.    No concatenation:- No concatenation :- No concatenation  Longic won’t concatenation thr data even files are common.

*Comment Id with ‘This is referring employee ID’;

*Comment Table tab with ‘This is an employee Table’;


Note: -Let V No. of Rows = No. of row(‘TAB’);


LOAD year, month as month , Sales as Sales resident transact

where recno() >= (NO of Row’s(‘transact’)-100);

LOAD REC (no() as rec, year month as month, sales as sales

Resident transact where Rec no() >= No of Row’s (‘Transact’)-100

    In Auto concatenation, the field name must be same.

    In Feild Force concatenation, the field  name must be different.

    In No Concatenation, the field name must be same.


MAPPING : – To Map the key values.

Main Table:

Load*Inline [

Rec     .        Name

1    .    Vivek

2    .     Dinesh

3    .    Venky

4    .    Harsha

5    .    Nandha

6    .    Surya

7    .    Vamsi

8    .    Mallu

9    .    Madhu];


MAP Table:

Mapping Load * Inline  [

Rec    Name

1.    ,    One

2.,    Two

3.,     Three

4.    ,    Four

5.    ,    Five

6.    ,     Six

7.,    Seven

8.,    Eight  ];

Tab :

Load Rec, Applymap(‘Map Table’, Rec,’******’)as atr, Name Resident main table;


*Drop: – To remove the unwanted tables and wanted fields is removed the application.

Drop Table Main Table;

Drop Table Main Table, Tab;

Drop field Rec, Name;

Drop fields rec, Name from Main Table

Apply Map(): – To map the values in a mapping table

Apply map(Map table name, expression , default value)


Map Table 1:

Load * Inline [

Month, Desc

1,    Jan

2,    Feb

3,    Mar

4,     Apr

5,    May

6,    Jun

7,    July

8,    Aug

9,    Sep

10,     Oct

11,    Nov

12,     Dec            ];

Apply map (‘Map Table’, Month, ‘****’) as the month Resident Trans;



Load [Transaction ID]



Apply map (‘MapTable 1’, Month,’****’) as month,


[Sales man ID]

[Product ID]

[Serial No]

[ID customer]

[List price]



*QlikView Data file  Stores table information

*Qlikview Nativ file Read &write only by QV software

*Stores One Tab information only


Advantage of QVD files:-


Diagram a


1.    Reduce the burden on the server

2.    Reduce the Network traffic

3.    Compress 60 – 90% of data

4.    10-100 time faster

5.    Consolidating in multiple QV    applications

6.    Incremental Load   Loading of only newly loaded (or) added records.


Storage form of QVD Files:-

•    Symbolic tables

•    Data tables

Employee ID:-


ID        Name        Salary         City

100        XXX        1000        NRL

101        OOO        10,000        HYD

102        111        1001        Delhi

103         222        1002        Mumbai




Symbolic Tables:-

ID                    Name                    City

Value    Pointers            Value    Pointers        Value    Pointer

100    1000                XXX    2000            HYD    1003

101    1001                YYY    2001            Chennai    1004

102    1002                ZZZ    2002            Delhi    1005

103    1003


Data Table: – In data table it stores pointer values

ID    Name    Salary    City

1000    2000    2500    3000

1001    2001    2501    3001

1002    2002    2502    3002

1003    2003    2503    3003





Bit Stuff Pointers: – How many bits required to store the pointers.

Byte Stuff Pointers: – How many bytes required to store the pointers.


Creation of QVD files:-

Creation of QVD files


Automatic            Manual



Buffer Load            Export            Store command


    Store Trans into Trans qvd(qvd     )

    Store Trans into Trans(qvd);

    Store * form Trans into Transqvd (qvd);

    Store year month sales as sales from Trans into trans.qcd(qvd);

    Store year, month as month, sales as sales from Trans into

(H :\Harsha\Bataches\ Batch 12\ Trans .qvd(qvd);

    Store year, month as month, sales as sales from Trans into

    (H:\Harsha\Bataches\ Batach12\ trans. Txt (txt);


BINARY LOAD: – Loading the data from another QlikView document including section access data.


Limitations of Binary Load:-

    It won’t Load the logout information and variable information.

    It should be the first statement in the script.

    It allows only one binary load in the script.

    To load the data from the previous document.

*Buffer Load: – T create and maintain the QVD files automatically

Trans :

Buffer Load [Transaction Id]

Buffer (State after 7 hours ) Load [Transaction ID]

Settings  Document properties  General  Keep QVD Buffer

–>Setting user preferences General  keep orphaned QVD buffer.


Conditional Functions :-            Inter record functions:-

1.    If()                        1.exists()

2.    Alt()                    2.Previous()

3.    Pick()                    3.Peek()

4.    Match()                4.field value()

5.    Wild matching()            5.field value()

6.    Class()


Conditional Functions:-

1.    If() :-Let VVal = if (5=10,1,0)//0

Load[ Transaction ID]


If (year = 2006,2008,year ) as year;

Replace (year, ‘2006’,’2008’) as replace year;



[Sales man ID],

[Product ID],

[serial no],

[ID customer],

[List price],


[Gross margin]

Nested if Statement:-

Load    [Transaction ID]


Replace (year,’2006’,’2008’) as replace year;

If (year = 2004, 2005,

If (year = 2005, 2006,

If( year = 2006,2007,

If (year = 2007, 2008,

If (year = 2008, 2009, year ))))) as year;






[Gross Mergin];

2.ALT()  :-

Let Vdate = today()

Let Valt = alt (date # ($ (Vdate),’MM/DD/YY’);

date # ($(Vdate),’DD/MM/YY’);

date # ($(Vdate),’YYYY/MM/DD’);

date # ($(Vdate ),’MM/YYYY’),

‘No valid data format’);

3.PICK():- Pick the expression in the position value

Let Vpick = Pick (4, ‘A’,’B’,’C’,’D’,’E’); //D

Pick (Month ,’Jan’,’Feb’, ‘Mar’,’Apr’,’May’,’Jun’,’July’,’Aug’,’Sep’,’Oct’,Nov,’Dec’) as month 1,


4.    MATCH () : – Match function is used for case sensitive comparison.

Let VMatch = Match(‘A’.’a’,’b’,’A’,B’);


5.    Mix match(): –    Mix Match function is used for case insensitive comparison.


Let VMixmatch = Mismatch (‘A’,’a’,’b’,’A’,B’);

6.Wild Match() :- Case insensitive and wildcard comparision.

Let VWildmatch = Wildmatch (‘Jan’,’J*’,’Jaf’,’Ja?’);//1

Let VWildmatch 1 = Wildmatch (‘Jan’,’J*n’,’Jab’,’Ja?’);//1

Let VWildmatch 2 = Wildmatch (‘Jan’,’Jac’,’Jan’,’Ja?’);//1

Let VWildmatch 3 = Wildmatch (‘Jan’,’*n’,’Jaf’,’Ja?’);//1

Let VWildmatch 4 = Wildmatch (‘Jan’,’Ja*’,’Jab’,’Jac’);//3

Let VWildmatch 5 = Wildmatch (‘Jan’,’Ja*’,’ja?’,’Ja?’);//3

7.Class():-  Let Vclass = Class(5,10);

Syntax : – Class(value,interval, variable name,offsetvalue)

o/p: will come as (5, 10);            Values in Between


0< = x< 10            0-10,



Eg :- Class(15,10);            30-40

10<= x<20                40-50

Class (10,10);            50-60




Eg :-Class (13,10, ‘val’);

o/p :- 10< = val < 20

Eg:-Class (16,10,’Val’ , 5);

o/p :- 15 < = val <20

Eg :- Class(3,10,’Val’,5);                -55

o/p -5 <= Val < 5                5 10



1. Exist (1, year)

 1 for false

 0 for true

LOAD * from file1 . csv ;

LOAD * from file2. Csv where exists (ID);

LOAD * from file1.csv ;

LOAD * from file2.csv where exists (ID);


Tab:                Tab1:

LOAD * Inline[            LOAD* Inline [

ID, DESC            ID,  des

1, dgs                4, jdf

2, djgs                5, dgc

3,kjfh                6, dfs

4, jg                7, dnf

5  ,jhrg         ];        8   , kfh   ]:

TAB2 :

Load ID as id , Desc as desc resident Tab;

TAB 3:

Load ID as id, des resident Tab1 where existing (id, ID);

TAB 4:

Load ID as id1, Desc as desc1 Resident Tab ;

TAB 5:

Load ID as id, DESC resident tab1 where not exists (id, ID);

Previous() : – To fetch the previous value of fields

Load [Transaction ID]



—        —

—        —

—        —

—        —


If (Recno() = 1,0,  Previous (sales)) as previous sales,

For previous sales of previous sales

If (Recno() <=2,0, previous (previous (sales)) as presales,

Sales + if (Recno() = 1,0, previous (sales) as cumulative sum

Peek():-field name ,record num , table name

Peek (‘sales’,0,’Trans’) as first sales

[ Gross margin].

Let vfirst Rec = Peek (‘Sales’,0,’Trans’);

Let Vsecond  Rec = Peek (‘sales’,’1’,’Trans’);

Let V50th Rec = Peek (‘sales’,49,’Trans’);

Let Vlast Rec   = Peek (‘sales’, -1,’Trans’);

Let VRec = Peek (‘sales’, -2,’ Trans’);


Fetching Last 100 records in a table:-

Let Vlast Rec = Peek (‘Rec’, ‘-1’, ‘Trans’):


Trans 1:

LOAD * , Resident Trans where Rec No () >= ($(Vlast Rec)-100);

LOAD  Recno()as Rec Resident Trans where Recno())>=(No. of Rows(‘Trans’)-100);

Finding maximum and minimum sales in a table:-

Qualify *;

Load * Resident Trans order by sales.

To Avoid null Values:-

LOAD * Resident Trans where not is null (sales) order by sales drop table Trans;

Let Vminsales   = Peck (‘Sales’ ,0,’Trans2’);

Let Vmax sales = Peek (‘sales’,-1, ‘Trans2’);

Qualify *;

Un Qualify sales;

Trans 2:

LOAD Max(sales) as Maxsales, Min(sales) as Minsales Resident Trans;

Let Vminsales = Peek (‘Minsales’,0,’Trans2’);

Let Vmaxsales = Peek (‘Maxsales’,0,’Trans2’);


*Previous functions is used only within the load statement and it won’t  execute outside the load statement and  reporting records.

* Peek function is used within the load, outside the load and at the reporting objecting  objects

 Date and Time functions:-

Let Vtoday = Today();

Let Vyear = year (Today());

Let Vmonth = Month (Today());

Let V Day = Day (Today(1));

Let Vweekday = Weekday (Today());

Let V Weekstar = Weekstar(Today());

Let Vmonth num = (month (Today());

Let VNOw = Now();

Let Vtime = Time(now());

Let Vfloor = floor (now());

Let VMinutes = Minutes(now());

Let Vsecond = Second (now());

*To fetch the year, day, month we use make function:-

Make date (year, month, day)as date

In concatenation form the date will form as

Day & ‘1’ & month &’/’& year as date;

#  It is used to find the format.

GMT – Green which Median Time

Let  VGMT = GHM();

Let Vtime zone = Timezone();

Let Vavg = age (today (), ‘09/14/1989’);

Let Vdate = date (Today());

Let vdate1 = date (now());

Let Vdate2 = date(‘12/14/1999’);

Let VDate3 = Date(today(), ‘DD-MM-YY’));

Let VDate4 = Date(Date #(’12-03-15’,’DD-MM-YY’));

Let VDate5 = Date (Date. # (’12-03-15’,’DD-MM-YY’),’DD/MM/YYYY’);


Let VTimestamp = Timestamp #(’12-05-1989 12:03:57 AM’, ‘MM-DD-YYYY HH:MM:SS  TT’);

Let VTimestamp = Timestamp (Timestamp #(12-05-1989 12:03:57 AM’, ‘MM-DD-YYYY HH:MM:SS  TT’), ‘MM/DD/YYYY  hh:mm:ss TT’);

UTC – universal coordinated time

Let VUTC = UTc();

    We have to convert

    one function to another form w use date format

Note :-

    Date # function identifies in which format the date exists whereas date function converts one function to another into required format

    Timestamp # also identifies the format where the time exists in the fields

    Timestamp converts one format to another format.  i.e into required format.


Let V add Months  = add months (today(),10);

// add to months from current month.

Let V subtract months  = Add Months (Today(), -10);


// current Month to substract 10 months

Let V ad year = add year (today(),5);

Let Vsubstract year = add year(Today().-5);

Let Vyearstart = yearstart(today());

Let vyearstart  = yearstart(today(),5);

Let vyerastart2  = yearstart(today(),-5);


Let VMonthstart = Monthstart(Today());

Let VMonthstart1 = Monthstart(Today(),3);

Let VMonthstart 2 = Monthstart(Today(),-3);


Let Vweekstart = weekstart(Today());

Let Vweekstart1 = weekstart(Today(),3);

Let Vweekstart 2= weekstart(Today(),-4);


Let Vdaystart = Daystart(Today());

Let Vdaystart1 = Daystart(Today(),2);

Let Vdaystart2 = Daystart(Today(),-2);


Let YearEnd = YearEnd(Today());

Let VQuaterEnd = quaterEnd(Today());

Let MonthEnd = MonthEnd(Today());

Let WeekEnd = WeekEnd(Today());

Let DayEnd = DayEnd(Today());


To convert day Numeric value to current day Numeric value:-


    From 1899 to till

Let vday = Num(Today());

Let VNow = Num[Now(1);  No. of days 42147.430046….

    Time will be referred by now function i.e. referred in points

To Existing date to add 10 days

Le VToday = Num [Today() +10);

To see Date

Let VToday = Date(Num[today()) +10);

Let VQuaterstart = Quaterstart (Today());

Let VQuaterstart1 = Quaterstart (today(),5);

Let vQuater start2 = quaterstart (today(),-5);

Cross Table Load: – to convert columns into rows tab.

Cross table(month, date, 2(qualified fields) load year,day.Jan, feb, Mar, Apr, May, Jun from;

Generic Load: – to convert rows into columns


Generic load object





Into load: –  to attach the picture, images, sounds files to the field.

Info table:-

Info load country,


Bundle Info Load: – Bundle the Corresponding information to fetch the software even if file is not there it will run

Info load: – where to in this load it won’t reload if the file is not there.

Partial load: – instead of loading all the tables we have to load the particular required tables called partial reload


In add: – It adds the records in the table with the Existing records

Add only:- won’t reloads in normal reload, it executes  in partial reload

Ex: – if there are 4 tables with 6 records

Add         reload                partial Reload

24                       30

24                           36

24                        30

Ex :-

Add           Reload            Partial reload                     only        18                29

18                30

18                36


Replace: – In replacing if replace the previous table rec values

Replace        Reload         particular Reload

12            6

12            6

12            6


Replace only: – in this only instead that table it reload the remaining table

Replace only        Reload        Partial Reload

18             6

18            6

18            6


Load  * Inline [

Rec, Name

100, hay

101, sdf

102, dsjh

103, dsb

104, dsmb


Tab1 :


Interval match load:-

Value table:

Load * Inline [







55    ];

Match table:

Load  * inline [

From, To

0,    10

11,    20

21,    30

31,    40

41,    50

51,    60   ];

Interval match table:-

Interval Match (value) load from, to resident match table;

Interval Match (value) load from as from . to as to resident match table;


1.    o/p of this interval match load should have only three fields.

2.    If must and should have the from & to i.e Range of values i.e upper & lower values

3.    More than 3 fields did not get output.

Incremental load or data load;-

Instead of load all the record, we are loading only the new records or instead records eliminating the existing records.

1.    Append only [log files]

2.    Insert and update[no delete]

3.    Insert, update, and delete.

Load only new or changed records from the database the result should already be available, one way or another.

Buffer (incremental) load:-

A solution only for log files not for databases.

Incremental load;-

    Load new data from DB table (slow but few records)

    Load the old data from QVD file[ many records but fast]

    Concrete the old data & new data.

    Store the data in qvd file

    The procedure must be repeated for each table.


Different DB-changes:-

If source allows:

1.    Append only (log file)  log files only

Buffer (incremental)

Load * from log file .txt

2.    Insert only

    Newly added records

    Load only inserted records

    Can be any DB

    Needs the fields Modification

Qv-table: loading new data

SQL SELECT primary key, x, Y from

DB-Table where Modification Time >=

# $9ast excel time)#

[ fetching the old daa] AND Modification time <#$

( Beginning excel time)#


Load primary key, X, y from file  .qvd;

Insert and update:-

    Can be any database

    Load inserted and updated records

    Need the fields

Insert, update, and delete:-


SQL SELECT primary key, X, Y from DB-Table

Where Modification time >= #$ (last excel time )#;


Load primary key, X, Y from file QVD

WHERE NOT EXISTS ( primary key);

Inner join


STORE QV- table INTO file.QVD;

Insert only:-


Load *  Inline [

Id,     Name,     salary,      date

100,        xxx,        1000        1/12/12

101,    yyyy,    1001,    13/2/13

102,    zzz,    1002,    14/4/14

103,    AAA    1003,    4/9/14

104,    BBB    1004    11/12/14

105,    CCC    1005    12/5/15



Load * Inline [

ID    NAME        salary        date

100    karthik        20,000        1/12/13

101    Vivek        30000        13/12/13

102    Mani        40000        14/14/14

103    srinu        50000        14/9/14

104    charan        -100000    11/12/19

105    Madhu        100000        2/2/15

106    siva        1000000    3/3/15   ];

Let vlast Execdate =0;

If not ISNULL(QVD create time (‘Tab.Qvd’))

Tab1 :

Load Max(Date) as Maxdate from then Tab.Qvd(qvd);

Let vlast Execdate = Peek(‘Maxdate’,o.’Tab1’);

Drop table tab1;



Load ID as id, Name as name, salary as salary. Date as date Resident tab where date >= $(vlast Exec data);

If not ISNULL (qvd create time(‘Tab.Qvd’)) then concatenate


LOAD ID as id, Name as name, Salary as salary, Date as date from [H:\Batches\Batch 12\Tab.qvd] (qvd);


If No of ROWS (‘Qv-Table’) <> 0 and Scrip terror =0 then Store id as ID, name as Name, Salary as salary, Date as a date from QV- Table into Tab.qvd (qvd);


Insert and Update:


LOAD * Inline [

ID,          NAME,                  SALARY,               DATE

200,        Karthik,                                20,000,                 13/2/13

300,        Vivek,                    30,000,                  30/5/15

400,        Siva,                       40,000,                  4/9/14

500,        Madhu,                                50,000,                  11/12/14

600,        Venky,                  60,000,                  12/2/15

700,        Harsha,                 70,000,                  3/3/15];

Let vLast Exec Date =0;

If not IS null (Qvd Create time (Tab ‘qvd’)) then


LOAD Max (Date) as Max Date Resident Tab;

Let vLast Exec Date =peek (‘Max Date’, 0,’Tab1’);


LOAD ID as id, Name as name, salary as salary, Date as date Resident Tab Where Date>=$[vLast Exec Date];

If not IS null (Qvd create Time (‘Tab.qvd’)) then concatenate,

LOAD ID as id, Name as name, Salary as salary, Date as date From [H:\Batches\Batch12\Tab.qvd] (qvd) where not exists (id.ID);


If No of ROWS (‘QV-Table’)<>0 and Script error =0 then Store id as ID, name and Name, salary as Salary, date as Date from QV-Table into Tab. Qvd(qvd);

Insert, Update and delete:


LOAD * Inline [

ID,          Name,                  Salary,                   Date

200,        Vivek,                   1000,                     13/2/13

300,        Dinesh,                 2000,                     30/05/15

400,        Venky,                  3000,                     4/09/14

500,        Harsha,                 4000,                     11/12/14

600,        Nandha,               5000,                     2/2/15

700,        Siva,                       6000,                     3/3/15];

Let vLast Exec Date =0;

If not IS Null (QVD Create Time (‘Tab.qvd’) then


Load Max (Date) as Max Date Resident Tab;

Let vLast Exec Date =peek (‘MaxDate’, 0,’Tab’);

Drop Table Tab1;



Load Id as id, Name as name, Salary as salary, Date as date Resident Tab where Date >=$(vLast Exec Date);

If not ISNULL (Qvd Create Time (‘Tab.qvd’)) then concatenate

LOAD ID as id, Name as name, Salary as Salary, Date as date From [H:\Batches\Batch 12\Tab.qvd] (qvd)

Where not exists (id, ID);

Inner join

LOAD ID as id Resident Tab;


If No of ROWS (‘QV-Table’)<>0 and Script error =0 then Store id as ID, name as Name, salary as Salary, date as Date from Qv-Table into Tab. Qvd (qvd);


Master Calendar Creation:


LOAD Date ($ (vmin Date) + Rec No ()-1 as Temp Date Auto Generate ($(v Max Date) -$(VMinDate) +1) ;

Master Calendar:

Load Temp Date as date,

Year (Temp Date) as Year,

‘Q’ & Cell (Month (Temp Date) 3) as Quarter,

Month (Temp Date) as Month,

Day (Temp Date) as Day,

Week Day (Temp Date) as Weekday,

Year Start (Temp Date) as Year Start Resident Calendar;


Load max (Date) as max (Date, Min (Date) as min Date Resident Transact;

Let Vmin date =peek (‘min Date’, 0,’Tab’);

Let Vmax date=peek (‘Max Date’, 0, ‘Tab’);

LOAD Date as date Resident Transact order by Date;

Let Vmin Date = Num (Peek (‘date’, 0,’Tab’));

Let Vmax date=Num (peek (‘date’, 0,’Tab’));

Let Vmax date =Num (peek (‘date’, -1, ‘Tab’));


To enable the Design Toolbar:

View à Toolbar à Design


We can also open the toolbar

i)                    Layout à New Sheet Object à Display the Sheets.

ii)                   Objects à New Sheet Object

iii)                 Sheet Properties à New Sheet Object.


To List out the values in a particular field

àList box won’t show null values 8 duplicated

ð  Show Caption à enable or disable

ð  Other than expression window if we write expression anywhere we have to keep =’symbol first =if (year>= 2007, ‘Year>=2007’, ‘year=2007’)

ð  If we want to change the title of the expression based on our requirement.

ð  We must enable Allow minimize, Auto minimize, Allow Maximize for reporting objects because to save space.

ð  By default selected values in green colour and non-selected the value in grey colour.

*LOCK   —    We won’t able to select anything in the selected fields.

* Layout: Information about shadow intently

à If we don’t want shadow then select no shadow

*Theme Maker: We can create themes and we can apply.

*Apply Theme: We can apply Existed Themes.

* Conditional: if Zero à It will be hide.

If the reporting object is hiding then we can select them by selecting the Sheet Properties à Objectsà It display List of Objects.

  • Conditional à 1à It show the data
  • We have to enable options all.

Number: To represent the number in required format.



Asc   — 1st Select values in asc then remaining value in asc.

Desc – 1st unselected values in desc then Selected value in desc.

General: In this we can select the show frequency à no. Of times, the records are repeated (Count of the records)

èIf we want to display the percentage than we have to enable in percent

èRead Only  à We can’t enable to select data override locked field à If Select the fields even it is Locked.

Types of Searches:


General Numeric Functions:

DIV ()            odd ()                    Num Sum ()

Mod ()          even ()                 Num count ()

Ceil ()            Pernut ()              Non Avg ()

Round ()      Combine ()         Num Max ()

Floor ()         Trace ()                 Num Min ()

Num ()

Num # ()


èShow the values 1 to 100 values in List box

èShow the list box field names in another list box.

èEach record one colour

èColour Mix()

Colour Functions:

Colour ()                              light green ()                      RGB (0, 0, 0)                       –              Black

Green ()                               light red ()                           RGB (255,255,255)           –              White

Blue ()                                   light Blue ()                         RGB (255, 0, 0)                   –              Red

Red ()                                    RGB ()                                   RGB (0, 255, 0)                   –              Green

Yellow ()                                                                              RGB (0, 0,255)                    –              Blue

White ()                                                                               RGB (128,128,128)           –              Yellow

Megneta ()                                                                         RGB (128, 0, 0)                   –              Light Red

Orange ()                                                                             RGB (0, 128, 0)                   –              Light Green

Black ()                                                                                 RGB (0, 0,128)                    –              Light Blue

Tree View:

Load * Inline [


Z –A-B-C-D



  • Input Box:

Combination of Multi field values in a box is called list box

  • Multi Box:

Compact way of representing multiple list boxes.

Input box:  Sum ({$<year = {“$(year)”}>} Sales}


  • Text Object:

ð  Time(Now)         –              To display only time

ð  Time (Now (0))                 –              To display Starting time of application.

ð  Time (Now (1))                 –              To display the every time function referred time.

ð  Time (Now (2)) –              When document is opened that time will be displayed.

ð  Time mode 0 à when the script is error.

ð  Timer mode 1 à Currently time

ð  Suppose if we don’t place = Symbol then it work evaluates the expression it gives same data.
Timer mode 2 à when the document created.

ð  To show the today data à =Today()


When we click on this it performs some actions.

–          We can add the actions

–          When we perform the action it will change the colour otherwise it won’t be.


Toggle Select – Switching the values

Pareto Selection: Based on the expression, % value

Lock field: Selected particular field is locked.

Layout: Activate Object

Activate Sheet

  • Book Marks:


  1. User Bookmarks à Available for particular person
  2. Document Book marks

We can see the Book mark ID in

Book mark à More àID

Launch: when we want to launch any app.

  • Document Chain:

How we will implement document chain in QV & Concept (Task)

Run macro: To execute the Java Scripting Code

Set Variable:

We can give the value to the field.

Show information:

If we want to remove all the data in application

Go to file  à Reduce data à Remove all values.

Remove all data:

It will remove all values in the sheet. Reduce data à Remove all values.


In QlikView X-axis à Dimension

Y-axis à Expression or metrics

Aggregate à Grouping of data

It consist sum (), Max (), Min ()

Avg (), only (), Count ()

When we select Read only we can’t change data in Chart.

But when we perform on other reporting object.

If will reflects in the Charts.


Even if you select in other reporting objects it will not reflected.

Data Alignment:

A. Table or set of tables which are not having any associations with the remaining tables in a data table. Should be select only one value if more than one value is selection if should shows the error.

To do this we have to go

Chart properties à General à Get Selected Count (year) =1

To display our message go to error message then write the data what we have to display if above condition is not satisfied.

*Fast type Change:

To convert one type of chart to other. When we select what type of Chart we required there are enable at the caption or In Chart, when we select those they display the chart.

  • Colour :

To get each bar is different colours we have to select Colour à Multicoloured.

èTo get the fixed colours  à Persistent à Colours

èTo get the patterns instead of colours we have to enable on screen.

èEnable while printing if we want patterns at the print.

*Reference mode:

To set the reference value. Later we can compare the data by selecting remaining bars.


à Click on add calculated dimension if we want to give any condition.

èParticular condition à we have to enable Enable Condition.

èIf we don’t want to see null values in chart enable suppress when value is null.

èX-axis values are called Legend.

èTo mention the X-axis name we will the give the name otherwise by default it gives the field name.

èWe can add the comment at comment


It shows the data like the video

It shows the data in Single bar.


It shows the chart for every year we can select how many no of rows we have to get number of columns we have to get

Groups: grouping data

Group –  1.Drilldown group – hierarchy of show the data

2. Cyclic group – Separate every one

Dimension Limits:

To show total Sales in all years enable “show total”

Enable Condition option:

To show or hide the expression or dimension values.


For renaming we will give the name in Label.

Relative:  % wise data showing we will go for Relative option.

Invisible: Chart will not appear

Bar: It will show bars

Display options:

èValues on data points

èTo show the bar corresponding Values1

èText on axis : axis corresponding value

èText as pop –up : date Will be shown max, min

Bar Chart Width:

èValues on data points: It will show the values on all bars.

èPresentation: Plot values inside the Segments.

èPop –Up Labels:

Suppress zero – values:

Suppress zero values in year

Suppress missing values:

Limit Legen (Character) à 4

Enable X-axis Scroll bar à 5

èAxes:  X-axis, Y-axis

èExpression axis:

Static Minà Starting point on Y-axis

Static Max à Ending Points

Static Step à Gap between each step

Show Grid à Show the Corresponding Bar lines.


èDimension axis:

Primary Dimension labels

Continuous à Point wise it show data

Stagger labels

Reverse stagger

Fore Cast

Back Cast

*Bar Chart:


Adding previous all data

èGrouping and stacking is possible only in Bar chart and Mekko Chart.

èIf we 3 dimensions then in this both Grouping and stacking is involved.

èMaximum number of dimensions for Bar Chart is “3”


It is having one bar and values are one above the other.

Grouping: Having number of bars

  • Line Chart:

ð  Line Chart is used for Trend analysis or movement analysis.

ð  In this we can show by using Line or symbols

Dynamic Colour:

Navigation (+) double click à back ground colour.

If (sum (sales) <=100000, Light Red (),

If Sum (Sales) <=400000, Light Gray (), Light Green ());

Quarter Colour:

= if (Ceil (month (3) =1, light red (),

If (Ceil (month (3) =2, light blue (),

If (Ceil (month (3) =3, Light Gray (), light Green ())));

èTo show the data from particular years.

If (Year> 2007 and Year <= 2009, Year)

Suppress is enabling when null value is delete.

Setting à user preference à Save à Use Backup.

Show the values in Particular bar

If (Year =2008,1,0).


  • Pie Chart:

Pie Chart is used when percentage analysis.

Pre Pop Out: Separated into sectors.

Properties à Expression à(+) double Click à Piepopout à Give Value other than zero.

Maximum number of dimensions for pie Chart “1”.

To Separate the Particular Sectors:

Properties à Expression à (+) Right Click on Expression à Pie Pop up Click à Give expression as if (year =2009, 1, 0)


  • Radar Chart:

èIn this X-axis is around 360

èLine Chart and Radar Chart are similar but here X-axis is around 360

èMaximum number of dimensions is “2”.


*Mekko Chart:

Differences between Bar Chart and Mekko Chart?

Bar Chart:  In this bar length is different but its width is same.

Mekko Chart:  In this bar Width is different but its Length is same.

Maximum number of dimensions of Mekko Chart is “2”.

In this when we give 2 dimensions it is in stacked Condition.


*Block Chart:

To represent hierarchy of data.

Representation of Negative values is not possible.


Funnel Chart:

Funnel Chart is used in flow analysis.

Maximum number of dimensions for funnel chart is ‘2’.

To get Height in equal proportion

Presentation  equal Segment heights

To get area in equal proportion

Presentation à Segment area proportion

To get Chart in triangle form

Presentation à tip Width –

Other wise –


*COMBO Chart:

To create combo Chart we must should give one dimension and 2 expressions.

It is Combination of Bar chart and line Chart

When we go for Combo Chart?

If two expressions are different i.e., Two Quantities are different then we go for combo Chart.


*Scatter Chart:

Scatter Chart is used for distribution of data

Maximum number of dimension “1” and expression “2”

When we add the 3rd expression it will show the size

When we add 2nd dimension it will shows the movement of data.

If we don’t give 3rd expression then all the bubbles are in same size.


*G nd Chart:

Minimum dimensions are “2”.

It is similar to Scatter Chart

Maximum number of dimensions for Grid Chart is “3”.

Bubble size represents the value of the data.

If 3 dimensions are given then 3 rd dimension is a show in pie Chart.


Table Box:

Straight table and picot table is Charts.

Straight table is single dimension and multiple expressions.

Pivot table is Single expression and multiple dimensions.

Accumulation is not possible in pivot table

Accumulation is possible in straight table.

Straight table is total summation

Pivot table is partial summations.

Pivot table is possible to convert rows to columns and table is and possible.

Visual Case: Only in Straight table and pivot table:

To Change the text colour and Back ground colour.


Gauge Chart:

Gauge Chart is no dimension

Presentation: Min Value & Max Value Modes

Show Scale: Major Units

Show Label on every  Major, Minor

Use to indicate KPI values


KPI: Key Performance Indicators

These are to improve the business performance.

Ex: Retail Domain, pharmacy domain,

Health Care domain, Insulation domain.


  1. Speedometer Gauge Chart
  2. Linear Gauge Chart
  3. Test tube Gauge Chart
  4. Text Gage Chart
  5. Digital Gage Chart
  6. Traffic gage Chart

Action tab is available in Gauge Chart


Authentication > User /admin

Authentication > If it is Authentication person they have authorization.

Security is given in

  1. Document Level
  2. Publisher Level


Document Level:

Security information is stored along with the Document in document level.

Publisher Level:

A single document is shared to the number of a document based on access.

Document Level

  1. Security information is stored along with the application.
  2. Hiding of data
  3. Provide some extent of security even if the file is downloaded.
  4. Less Memory

Publisher Level

  1. Single QV Document is divided into multiple documents based on access.
  2. Having data related to the access.
  3. No security if the file is downloaded.
  4. More memory.


File > Hidden Script > UN > Password >OK > Insert tab> Section access >Inline > Basic user Access table> all are entered in capital letters >OK >Section Access.

Settings > Document Properties> Opening tab >

ü  Initial data Reduction Based on section access.

ü  Prohibit Binary Load

Section Access:

Load * Inline [

Access, user id, Password, OMIT


USER, A, A, Year

USER, B, B, Month

USER, C, C, Day

USER, D, A, Sales];


Selection application:

Error Message: Sheet properties > General > Calculation Condition> get Selected count (year) = 3.


Set Identifiers:

1 – for all selections

$ – for current selections

$N – for Backward Selections

$-N- for forward selections

Bookmark / Bookmark ID – for Bookmark selections.

Set Expression:

An expression within the flower bracket is set expression.

Set Analysis:

Defining the text expression by using this analysing the data is called set analysis.

Syntax: Sum ({Set identifier < Set Modifier>} sales)

The Main Components of set analysis is Three

  1. Set identifiers
  2. Set Operators
  3. Set Modifiers

Difference between sum [sales], sum (total sales), sum (all sales)

Sum (All sales) > Irrespective of selection if will show all year sum (sales)

Sum (Total Sales) >Based on Selection it will show sum (sales)

i.e., for selected years, if shows the sum of the sales year.

Sum (Sales) >it shows the sum (sales) of each year.

$(vSales) > it is same as Sum (sales)

When we give sum (sales) it displays the sum (sales) of every year.

$(vSales 1) > variable > = sum (sales)

When we give = sum (sales) it displays the sum of all year sales.

  1. Set identifiers:


1-      For all Selections

$ – for current Selections

$N – for backward selections

$-N –for forward selections

Book Mark / Book Mark ID – for Book Mark selections.


Sum ({Set Identifier < set modifier >} sales)

  1. Set Operators:


–          Unary Minus

+      Union

–           Exclusion


/ Symmetric difference >except common values remaining extra values is both sets.

  • Sum ({$} sales)  – is same as sum (sales)
  • Sum ({1} sales) – irrespective of selections to show all values (sales)
  • Sum ({$1} sales)  –  previous selections data to display on one step back
  • Sum ( {$ -1} Sales)  – one set forward

1st we have to step back then only it display the forward selection. Otherwise, it displays no data.

  • Book mark à BMI à Sum ({BMI} Sales) à irrespective of selections it always it will show only book marks.
  • Sum ({$2} Sales) – Previous selection data to display on two steps Back.


Creating Bookmark:

1st select year values then go to Book marks > New Book marks.> give name >OK.

Getting Book mark ID:

Book mark > more > ID

Ex: Sum ({BMOI} Sales)

We can give either Book mark name or Book mark ID

Two types of Book marks:

  1. User Level Book Mark
  2. Document Level Book Mark

To get the Document Book mark:

Book mark >More>Select Document Book mark.

Sum ({document 1 bookmark 1} sales)

To get the user level document:

Bookmarks >more > select user book mark

Sum ({Server 1 Book mark 1} sales)



Q. How can we show the excluded values is using set expression?

Ans. Sum ({1-$} Sales)

Q. To get the currently selected values and Book mark values

Ans. Sum ({$+BMOI} sales)

Q. To get the only Current values except Book mark values

Ans. Sum ({$-BMOI} sales)

Q. To get other than common values in both current selection and book mark values?

Ans. Sum ({$ | BMOI} sales)


Set Modifier:

Set Modifier in should between angular brackets “[< >]”

  • To get the particular year = 2010 value using set analysis

Sum ({$ < Year = {2010} >} sales)

  • To get the year 2010, 2011 values

Sum ({$ < year = {2010, 2011} >} sales)

  • If we want to give String value then we have to give in single Quotes.
  • Numeric values may or may not be kept in single Quotes.
  • To write the search String value.

Sum ({$ < year = {‘201*’} >} sales}

Sum ({$ < year = {‘201*’} + {2007} >} sales)

Sum ({$ < year = {‘201*’} + {2007, 2008, 2009} – {2010} >} Sales)

Sum ({$ < year = {‘201*’} + {2007, 2008, 2009} >} Sales)

Sum ({$ < year = {‘201*’} + {2007, 2004, 2009} – {2010} >} sales)

  • To get the values of the year 2010, 11 in the 1st Quarter.

Sum ({$ < year = {‘201*}, Month = {1, 2, 3} >} sales)

  • To show all values

Sum ({$< year = {*} >} sales)

  • To show no values

Sum ({$< year = {} >} sales)

  • To show all the data to display irrespective of selection values.

Sum ({$ < year = >} sales)

  • To get the selected year value and given year value.

Sum ({$ < year = year + {‘201*’}>} sales)

Sum ({$ < year = year – {‘201*’}>} sales)

Sum ({$ < year = year * {‘201*’}>} sales)

Sum ({$ < year = year / {‘201*’}>} sales)


Sum ({$ < year += {‘201*’}>} sales)

Sum ({$ < year -= {‘201*’}>} sales)

Sum ({$ < year *= {‘201*’}>} sales)

Sum ({$ < year /= {‘201*’}>} sales)

  • To take the existed variable instead of our giving values

To create input> Variable to create one variable.

Sum ({$< year = {“$[v year)”}>} sales}

  • To get the maximum year value by selecting year values.

Sum ({<year = {$(=max (year))”}>} sales)

  • To get the minimum year value by selecting year values.

Sum ({<year = {$(=min (year))”}>} sales)

  • To get the particular year value by selecting only one value

Sum ({<year = {$(=only (year))”}>} sales)

  • To get the previous year by selection of present year

i.e., if you select 2008 year we have to display 2007 year.

Sum ({$< year = {“$(= only (year) -1)”} sales)

  • To get the 2nd maximum year Selection.

Sum ({$< year = {“$ (= Max (year, 2))”}>} sales)

  • To get the particular selected year their previous all year values as display.

Sum ({$< year = {“<=$(= only (year))”}>} sales)

  • To get the year values from 2005 up to max selected year

If (year> 2004, sum ({$< year = {“>= 2005<=$(=max (year))”>} sales))


YTD – Year to Date

QTD – Quarter to Date

MTD – Month to Date

WTD – Week to Date

LYTD – Last year to Date

LQTD – Last Quarter to Date

LMTD – Last Month to Date

Make Date (year, Month, Date) as Date

Sum ({$< Date = {“>=$(= year Start Date)) <= $(=Max (Date))”} Sales)


Sum ({$< Date = {“>=$(= year Start (Max (Date))) < = $ (= max (Date))”}, year =, Month =, Day =>} sales)


Sum ({$< Date = {“> =$(= Quarter Start (Max (Date))) < = $(=Max (Date))”}, year=, Mont=, Day=>} sales)


Sum ({$< Date= {“>=$(=Month Start (max (Date))) <=$(=Max (Date))”}, year =, Month=, Date= >} sales)


Sum ($< Date = {“>=$(= Week Start (max (Date))) <=$(=Max (Date))”}, year =, Month=, Date=>} sales.


Sum ({$< Date = {“>=$(=Year Start (Max (Date),-1)) <= $(=Max (Date))”} year =, Month=, Date=>} sales).


Sum ({$< Date = {“>=$(=Quarter Start (Max (Date), -1)) < = $(=Max (Date))} Year=, Month=, Date= >} Sales).


Make date (year, month, date) as date

Sum({ $< Date ={ “ >=$ ( + yearstart date)) <= $(=Max(Date)) “} sales).


Sum ( { $ <date = { “>=$(= year start (Max(Date))) <= $(= Max(Date))”}, year =, Month +, Day+ >} sales).

QTD :-

Sum({ $ < Date ={ “> + $ ( =Quaterstart (Max(date))) < =$ (=Max(Date)) “}, Year =, Month =, Day=>} sales).


Sum({$<Date = { “> = $(=Monthstart(Max(Date)))<=$(=Max(Date))”} year= ,Month =, date =>} sales


Sum($ < Date={“>=$( =Weekstart(Max(date)))<$(=Max(Date))”},year =, Month=, date=>} sales


Sum({$<Date ={“>=$(=yearstart(Max(Date),-1))<=$(=max(date))”} year=, Month=.Date=>} sales).


Sum ({$< Date={“>=$(+Quaterstart(Max(date),-1))<= $(=Max(date))”} year=,Month=,Date=> ) sales).


Sum ({$<date={“>=$(=Quaterstart(Max(date),-1))<=$(=Max(Date))} year=,Month=,date=>} sales).


Sum ({ $ <date={“>=$(=Monthstart(Max(date),-1))<=$(=Max(date))}, year=,Month=,date=>} sales).


Sum ({<date={“>=$(=weekstart(Max(Date).-1)<=$(= Max(date))}.year=,Month=,Date=>}sales}.

Ultimate States:-

Comparative analysis by using ulcerative shades:-

Yoy-year over year

Setting document propertiesgeneral alternate states ad state1 ok add state 2 ok.

Two list box year fields state 1-year


    Expressions ap

Sum ({sate1}sales)

Sum ({state2} sales)

Task from date to date comparison of date

What if analysis:-

To create a variable and create a slider object suppose if the values are increased by 10s then

    Sum(sales)+10/100 sum(sales) sum(sales(1+10/100)

This can be written as


In what-if analysis, we have to create


P(): – set of possible values

E() :- set of excluded values

P(): – If we want the sales of the particular customer in possible years then we use P().

Sum({$<year =p9{1<[id customer}={1010}>{sale)

Sum({$<[ID(customer}=p({1<[id customer] = {1010}>}

[Id customer])>}sales)


Sum({$<[sales manId}=p({1<{id customer}={1010}>[salesman ID])>}sales).

Sum({$<[ID Customer}=p({1<{id customer}={1010}>[salesman ID])>}sales).


E(): –  set of Excluded values


Sum ({$<year=E({1<[ d> customer]= {1010}>})>}sales)

For select id customer, the sales of excluded year values are displayed

Ad-hoc reports:-

Step 2: – Enable conditional  to show and hide the fields

substring count ( CONCT.(dimensions,’1’),’year’)

substring count ( CONCT.(dimensions,’1’),customer id’)

Substring count ( CONCT.(dimensions,’1’),’salesman  id’)

Substring count ( CONCT.(dimensions,’1’),’Product  id’)

step 3:-

Substring count ( CONCT.(Expression,’1’),’sum(sales))

Substring count (CONCT.(Expression,’1’),’Max(sales))

Substring count (CONCT.(Expression,’1’),’Min(sales))

Substring count ( CONCT.(Expression,’1’),’Avg(sales))

step 1:-

Tab:                                 Tab1:

Load * Inline [                    load * inline [

Dimensions                        Expression

Year                                  Sum (sales)

Salesman id                        Max (sales)

Id Customer                        Min (sales)

];                            Avg (sales)


Link table & Concatenation:-

Concatenation: – if all fields are same having same granularity.

Link table: – If all the fields are same but with different granularity

Step 1-  form the complex key

Step 2- concatenate the data from all synthetic tables

Step 3- drop the fields from corresponding fact table

    Auto number () is better  then concatenation because it is used ti improve that

    Auto number () purpose:-

Generate the numeric value instead of string


Fact 1:-

LOAD year-month  &‘1’ & cuts key & “1” & logo key & ‘1’ & hierarchy key as a key field.

Year month,

Cuts key,

Logo key,


Fact 2:-

Load      year-Month & ‘1’ &  cuts_key & ‘1’ & the logo_key & ‘1’ & Hierachy_key as Keyfied.







Fact 3:-

LOAD year-months & ‘1’ & cuts-Key & Hierarchy-key as key fields





Link Table:-

LOAD     key field,




Hierarchy-key resident fact1;


Load  key field


Cuts-key’ [hierarchy-key, resident fact 2;


Load key field



Hierarchy-key from fact 3;

Drop fields year-month,



Hierarchy-key from fact 2;

Drop fields year-month,


Hierarchy-key from fact 3;

Slowly changing dimensions:-


Type 0 –

Type 1    –

Type 2  –

Interval match:-

Interval match(transaction date,SPID)

LOAD distinct from date to date,spID Resident sales people dyn sales people dyn:


SPID & ‘1’ & from Date & ‘1’ & Todate as [SPId

+ interval ]


Load trasaction date, product id, amount, SP ID As TMPSP ID,SPID & ‘1’ transaction date as [SPID + transdate] from Transactions;

TMP bridge table:-

Interval Match( Transaction date,tmp spID)

Load District from date, SPID as Tmpsp ID

Resident sales people Dyn;

Bridge table;


Tmp SPID & ‘1’ Transaction date as [spid + transdate]

Tmp SPID & ‘1’ from Date & ‘1’ & to date as [SPID + interval]

Resident Tmp-bridge table;

Drop field tmpSP Id;

Drop table tmp Bridge table;

Tap Bridge table

Interval match (transactional data, Tmp SpId load distinct from date, to date, SpID as TmpspID resident sales people Dyn;

Left join( transaction)

Load TmpsPId.a transaction date,

tmpSPId & ‘1’ & from date & ‘1’ & Todate as [SPID + inyterval} resident Tmp-Bridge table;

Drop fields tmpSPID;

Drop table Tmp Bridge table;

    Gauge chart has zero dimensions

    No –ve values –block chart

    Rank analysis-bar char

    Gauge chart has only action tab.

    Percentage-pie chart

    The trend analysis (or0 movement analysis is line chart.


    To create bar chart

    Aggr(sum(sales),year)

    Aggr(rank(sum(sales)),year)

    If(aggr(rank(sum(sales)),year)<=3,sum(sales))

Maximum of three years

    Maximum of one year

If(aggr(Rank(sum(sales)),year)<=1 ,sum(sales)).

    If (year = 2011,1,0)              show value  write expression

    Maximum sales to show the data

If (aggr(Rank(sum(sales)),year)-1,1,0)

If(aggr(rank(sum(sales)),year)<= 3,1,0)

    Pie chart pie popout


Background color:-

If(sum(sales) <=10000,light red)

If(sum(sales)< 4000,light blue, light green)

If maximum month is displayed:-


First sorted value(monthmaggr(sum(sales),year,month))



1. Document reports   — everyone can access

2. User reports —Only user access the reports

Reports-Edit reports

                Count sectionsYear, Quarter, MonthCompany logo



Reports -report settings

Show she wise properties – tings – Document properties – sheet

Triggers :-

Setting -Document properties – Triggers Tab-

  1. Document even triggers
  2. Fields even triggers
  3. Varic even triggers
  4. Sheet even triggers


Remove data:-

Files reduce data-Remove all values.

*Types of cal’s:-

CAL –  Client Access usence.

1. Named CAL – user id, Password to access the documents

2. Document CAL – Any document has password and user id.

3. Anonymous users – No. user id, password

Usage CAL – to login only seeing the documents only

Session CAL – 15 minutes of time.


*performance tuning or Optimization tuning:-

1.Maintain QVD files1.Minimizing &Restoring of objects
2.Avoid synthetic keys2.Set analysis instead of nested if
3.Star Schema3.Avoid usage if macro, triggers
4.Dropping unwanted Fields or tables4. Better to implement complex expression at scripting level instead if at the reporting level.
5.Mapping instead joins
6.usage of auto number ()
7.Remove extra used variables
8.Incremental data load



*2- Tire architecture (or) 2- Stage architecture :-


1 .QVD flies

2. QVD files —-1.Base QVD’s

2. Transformed QVD’s

3. Include files

4. Images/Pictures.


  • 3- Tire Architecture:-





Setting Up Our Environment:–

  • Qlikview Development
  • Airline Operations

*0.1Sourced data

  • Dimension tables
  • Fact tables

2. Workbooks

3. QVD

*  source

* Transformed

* Applications


*Direct Discovery:-

1. Instead of sorting all the data we store the particular data [In memory (RAM)]

2. Refer the load statement we have to place “Direct Query”

3. It is four types

a. Dimensions

b. Measures

c. Details

d. Detach


Direct Discovery:-

It is a new feature added in 11.1

Field typeIn- MemoryForms AssociationsUsed in list boyUsed in chart expressionUsed in table box













YesIn Agg()ExpressiononlyNo












Sales – Order – Header-Join



AW2012. Sales . Customer . Customer Id as Custer Id

AW2012. Sales. Customer. Sales Order Head. Sales Person Id as Sales Person I.

AW2012. Sales.  Sales Order Header. Sales Order Id as Sales as order ID,

Product ID,

AW2012. Sales. Customer. Territory ID as Territory ID,

Order Date

Native (‘Month ([Order Date]’) as order month,

Native (‘ Year([Order Date])’) as order year


Sub Total,


Total Due,

Order Qty



Due Date,

Ship Date,

Credit card Approval code,

Person ID,

Store ID,

Account Number,


Modified Date

From  AW2012. Sales. Sales Order detail

JOIN AW2012. Sales. Sales Order Header

ON (AW2012. Sales . Sales Order Detail, Sales Order ID=



Step1. Edit script

Step2. Given window Title as ‘comparative Analysis ‘ and  Create the bar chart dimension ‘year’, Expression ‘Sum (sales)’

Step 3.Go to document properties, alternate states and name as state 1 and Add state2.

Step4. Change the Expression in bra chart as

Sum ({State1} Sales) and add another expression

Sum ({State2} Sales).

Step5. Go to New Sheet Objects -List Box

In General – Title as State1 and alternate State as state 1 and select field year and another List box

Title as state 2 and alternate states as state2 and select field as the year.

Step 6: – And compare the both state1 and state2 in comparative analysis bar chart. [/sociallocker]