some Qlikview

Qlikview ..

1.Hide field:
tag Fields field1,field2 with '$hidden';
the above script is used when you don't want to show that field selection in current selection box. 
to hide a field from everywhere (except the data model viewer) use 
SET HidePrefix = '_' ; (here instead of underscore we can use any character)
and then prefix the field name with that character. e.g. field1 as _field1.
this field will also not show in current selection and will come under system fields and you can see them by going into any window where you can write expression and check show system fields.
Same thing you need to do if you want to show system variables.

2.EDX Trigger:
echo off
"server location\QvEDXTrigger.exe" -t="<file name>.qvw" -v > server location\d$\Qlikview_Job_Log\<log file name to be generated>.txt
this is the triggering i used , don't remember how , search google for proper understanding.

3.List Box Multi Selection:
='('&chr(34)&concat(DISTINCT <field name> ,chr(34)&'|'&chr(34)) &chr(34)& ')' 
this code allows to select multiple field in different list box using the trigger on some other list box, e.g. we have two list box country1 and country2 and they are not connected and you want to select in country2 the same values selected in country1.

4.List Box hide grayed out values:

=aggr(only({<<field name>=>}<field name>), <field name>)

we are ignoring the field selection inside the only function as we don't want the selection of this particular field on itself, try it its good.

5.String in set analysis:
=only({<<some field>={"$(=$(<variable>))"}>}<some field>) 
e.g. our variable is var1 and its having value of field field1 then {<field1={"$(=$(var1))"}>}
** in case of a number also the above will work if not try removing the quotes.

6.Expression in set analysis:

=only({<sales={$(=Max(sales))}>}Year)  

=sum({<[Order Date]={">=$(=Min([Order Date])) <=$(=Max([Order Date]))"}>}Sales) 

7.Some kind of forced sorting not sure:
=pick(match(only({1}Month),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),1,2,3,4,5,6,7,8,9,10,11,12)
this is a simple example where we are forcely sorting the month - i had an issue where sorting was not working so i used this.

8.Wrote below code to load last 2.5 months data part wise as there was issue with loading all the data at once.

for i = 1 to 5

LET th = Pick(match(i,1,2,3,4,5),2,2,1,1,0);

if i = 1 or i = 3 or i =5 THEN
LET greaterThan = Date(AddMonths(MakeDate(Year(Today()), month(today()),01),-$(th)),'YYYY-MM-DD')&'T00:00:00.000Z'; // time format used in the extraction source
LET smallerThan = Date(AddMonths(MakeDate(Year(Today()), month(today()),15),-$(th)),'YYYY-MM-DD')&'T00:00:00.000Z';
end if;

if i = 2 or i = 4 THEN
LET greaterThan = Date(AddMonths(MakeDate(Year(Today()), month(today()),15),-$(th)),'YYYY-MM-DD')&'T00:00:00.000Z';
LET smallerThan = Date(AddMonths(MakeDate(Year(Today()), month(today()),01),-$(th)+1),'YYYY-MM-DD')&'T00:00:00.000Z';
end if;

Let vLoopCount=i;

TRACE greaterThan - $(greaterThan);
TRACE smallerThan - $(smallerThan);
TRACE loop - $(vLoopCount);

<table name given>:
LOAD 
     <column names>;
SELECT
     <column names>
FROM <table name>
Where <datatimefield>  >= $(greaterThan)
and <datatimefield> < $(smallerThan);

if NoOfRows(<table name given>)>0 THEN
Store * From <table name given>INTO <qvd path and name>_$(vLoopCount).QVD;
DROP Table <table name given>;
ENDIF;
NEXT;
EXIT SCRIPT; 

9.Crosstable Load: crosstable

this is used to create a crosstable i.e. if we want to some column values as rows.
e.g. we have a table like below (just for demo here we are inline loading the table)

Table1:

load * Inline [
name, jan, feb, mar
abhi, 200, 300, 250
]; 

then we write the below script

CrossTable1:

CrossTable(month, sales, 1) // this means name the second last column created which will have column values as month name the new column having the data as sales and the last '1' (also default is 1) mean how many columns to skip in the original table (Table1) for doing the crosstab.
LOAD
*

Resident Table1;
DROP Table Table1; (drop table as we don't want the previous table)

after the above script the new table (CrossTable1) will be like below

name, month, sales
abhi, jan, 200

abhi, feb, 300
abhi, mar, 250

10.Generic Load: generic
it's kind of opposite of crosstable load, if we want to create seperate fields from a column values then we load that table as generic load.
The generic prefix unpacks a tall table, creating one field per attribute value. This is similar to pivoting a table, except that it results in a separate table per field created. (definition from help.qlik)

Table1: // again we creating an inline table for demo 
load
* Inline [
name, month, sales
abhi, jan, 200
abhi, feb, 300
abhi, mar, 250
];

GenTable: // generic load
Generic
LOAD
*
Resident Table1;
DROP Table Table1; 

the above script will create three tables named GenTable named

GenTable.jan, GenTable.feb, and GenTable.mar (as here we have three distinct values in the attribute field), * also remember in generic load the last columns is value column and second last column is the attribute column and rest all will be treated as key columns.

in above script we have only one key column so the three table will have simple association but if we have more than one key column we will have association through synthetic keys.

now we don't want three tables, we want a single table in proper format, below is the full script for a single key table

Table1:
load
* Inline [
name, month, sales
abhi, jan, 200
abhi, feb, 300
abhi, mar, 250
];

GenTable:
Generic
LOAD
*
Resident Table1;

final_table: // just for getting the keys which we will later use for joining
LOAD Distinct
name
Resident Table1;
DROP Table Table1;

for i=0 to NoOfTables()
TableNames: // creating a table with table names of generic loaded tables
LOAD
TableName($(i)) as TableNames
AutoGenerate 1
where WildMatch(TableName($(i)),'GenTable.*'); // to make sure we only load the generic loaded tables here
NEXT;

for i=1 to NoOfRows('TableNames')
let tname = FieldValue('TableNames', $(i)); // getting table name from the table TableNames
TRACE $(tname); // just for debugging purpose not compulsory
Left Join(final_table)
LOAD *
Resident $(tname);
DROP Table $(tname); // dropping tables as we keep joining with the final table haiving keys
NEXT;

DROP Table TableNames; // dropping table having table names 

for table with multiple keys we will need to create a single key , you can do this by using
the below code in the main table and using only single key field instead of multiple key fields and then in the final table you can separate the fields using the SubField function
Autonumber(keyfield1 & '|' & keyfield2 & '|' & keyfield3) as Key // in case we had 3 key fields

11.Hierarchy and Hierarchy Belongs to:
hierarchy, hierarchy belongs to 
when we have a scenario like we need to create hierarchies for example, we have columns called employee_name, employee_id, manager_id, now we need to define a hierarchy which will show who is who's manages and who comes under whom e.g.
we have a group manager under him we have 3 senior mangers under each senior managers we have some manager under each manager we have some leads and under leads we have some developers and testers.
or second scenario, we have countries within countries we have states then cities then district and so on.

Hierarchy and HierarchyBelongsTo are two functions which make the above situation easy to handle.
Hierarchy function // here arguments after first NodeName are optional

Hierarchy(NodeID, ParentID, NodeName, ParentName, NodeName, PathName, '\', Depth) LOAD * inline [
NodeID, ParentID, NodeName
1, 4, London
2, 3, Munich
3, 5, Germany
4, 5, UK
5, , Europe
]; 

the above code will create the below table structure

NodeID	NodeName	ParentID	ParentName	NodeName1	NodeName2	NodeName3	PathName	Depth
5	Europe		-	Europe	-	-	Europe	1
3	Germany	5	Europe	Europe	Germany	-	Europe\Germany	2
2	Munich	3	Germany	Europe	Germany	Munich	Europe\Germany\Munich	3
4	UK	5	Europe	Europe	UK	-	Europe\UK	2
1	London	4	UK	Europe	UK	London	Europe\UK\London	3

the above table and code are easy to understand as we see the first three columns are direct columns from the main table ParentName column is simply the NodeName of the ParentID, the NodeName1 is the top most value in hierarchy and NodeName2 is below that and so on, PathName shows the values from begining to end in an hierarchy for a particular NodeName and Depth will tell us the hierarchy depth.

HierarchyBelongsTo function // here only DepthDiff optional

HierarchyBelongsTo (NodeID, AncestorID, NodeName, AncestorID, AncestorName, DepthDiff) 

LOAD * inline [
NodeID, AncestorID, NodeName
1, 4, London
2, 3, Munich
3, 5, Germany
4, 5, UK
5, , Europe
];

the above code will create the below table structure

NodeID	AncestorID	NodeName	AncestorName	DepthDiff
1	1	London	London	0
1	4	London	UK	1
1	5	London	Europe	2
2	2	Munich	Munich	0
2	3	Munich	Germany	1
2	5	Munich	Europe	2
3	3	Germany	Germany	0
3	5	Germany	Europe	1
4	4	UK	UK	0
4	5	UK	Europe	1
5	5	Europe	Europe	0

the above table is also easy to understand, from this table if we select anything in the AncestorID column or AncestorName column it will filter out same value and values below it in hierarchy in the NodeID and NodeName column. Here AncestorID and AncestorName are same as the ParentID and ParentName in the Hierarchy function.

The way we generally use hierarchy and HierarchyBelongsTo functions is that we will rename NodeID, to something else lets say NodeID_1, now as NodeName column will be there in both Hierarchy and HierarchyBelongsTo table it will get associated, now if we select any value from the AncestorName/AncestorID column we will get the proper hierarchy.

You can create a table box as the first table shown below the Hierarchy function explanation and then take a list box of ancestor name then you can see how the table gets filtered out based on selection of a value from the AncestorName list box.

12.Performance tuning: someLink
In general performance is improved by moving the “problem“ from application objects to the script driven database, This is often a trade off situation. Response time is enhanced and ad hoc capability is diminished.

- If clause involving text comparisons is generally expensive. Solutions can be to map text to numbers e.g. by using autonumber and/or do the test in the script.

- aggregation at script only if the lowest level granularity data is not required to be shown in the presentation layer.
- The logic of nested If ... then else ... is conceptually easy but can often become troublesome to administer. We have seen cases with hundreds of nesting levels. This is both memory as well as CPU intensive.
-  Using the extended intervalmatch syntax to resolve slowly changing dimension problems

- QlikView automatically evaluates if a Field is to be treated as numeric, text or general. Fields evaluated as text will be sorted as text which is the slowest sort operation. This can be replaced manually to sort by load order. If sorting of list boxes etc is not needed, turn it off.

- Dynamic Captions and text objects,  Expressions in charts and tables that are defined in the expressions dialog are only calculated when the object is visible and data changes. They are e.g. not calculated when the object is minimized.On the other hand, if the object title is calculated this calculation is performed every time any change occurs. There are also numerous ways of defining show conditions, calculation conditions etc. These tests will also be performed at all times.

- The time functions e.g. Now() and Today() will be evaluated whenever a recalculation is required. Especially the Now() function can become quite costly since it causes a recalculation of the application every second.

- Island Tables, if you are using the island table for some selection or triggering or in if else then if you make any selection all the expressions will be recalculated, instead use variables to set some values and use those values, e.g. if you want to have a language filter then instead of making an inline language table and doing selections on that make text boxes and set some variable and use that variable in some if else instead of the table selection, but if the table is linked with the data model that is better.

- Separate the expressions, if we have too many expression in if else or pick match instead use if possible separate the expression in the chart it self and then make them conditional.
- Check the response time of different objects, and see how you can improve the objects taking more time.

- Some general things community

Backend:

--Do all the required calculations in the script itself.

--Load only required data, for example if you are using only last 5 years data filter it out when you are loading data.

--Load only required columns and tables so that Qlikview file size would be reduced.

--If you are loading same tables in multiple dashboards then for the instance load from database and store it in QVDs and in other Dashboards load from QVD. 

Frontend:

--Use Set Analysis as much as possible.

--Don't display all charts in a single sheet or provide buttons to show and hide the charts.

--Don't display huge data in Tables, restrict user to do some selections so that the data would be reduced and load the table faster.

--Limit the calculations in front end and move it to scripting.

- Common things bardess
--Reducing data volume. The most common thing I have seen is people keep large amount of historical data in their applications. Maybe we should ask ourselves if it is really necessary to keep 10 years worth of data
--Pre-calculating most frequently used expressions. For instance, if lots of charts are about Last Year Sales, instead of using on-the-fly calculation to dynamically figure out last year’s sales, maybe we should populate a new Last Year Sales field in the data model. Sometimes these expressions can be pre-calculated as flags
--Pre-aggregating fact tables. If most charts are presenting data at a fairly high level, maybe we can create an aggregate table for the charts and only use detail level table when users want to view detail information. This way all charts are calculated quickly
--Merge tables. When a query involves multiple tables, QlikView joins these tables on the fly through keys. Less tables means less joins and therefore faster response
--Avoid string comparison. String comparison is very expensive in QlikView and it should be avoided as much as possible--Avoid big listboxes or tables boxes. Believe it or not, many people put listboxes or table boxes with millions of rows on the QlikView interface without know the tremendous system resources these objects consumes
--Avoid showing too many objects at the same time. Having too many objects not only takes time to calculate these objects, but also takes time to download these objects to end user’s browser

--Single threaded vs multi threaded Expression - if a same expression can be implemented using single and multi thread then use the multi threaded as its faster. eg. sum(sales) is multi threaded but count(distinct countries) is single threaded.

--Set Analysis vs If Statement - Set Analysis is more efficient because it takes advantage of pre-built in-memory indexes while an if function in an expression that often causes a full table scan

13.Set Analysis help.qlik 
Set analysis offers a way of defining a set (or group) of data values that is different from the normal set defined by the current selections.

Identifier	Description
1	Represents the full set of all the records in the application, irrespective of any selections made.
$	Represents the records of the current selection. The set expression {$} is thus the equivalent to not stating a set expression.
$1	Represents the previous selection. $2 represents the previous selection-but-one, and so on.
$_1	Represents the next (forward) selection. $_2 represents the next selection-but-one, and so on.
BM01	You can use any bookmark ID or bookmark name.
MyAltState	You can reference the selections made in an alternate state by its state name.

Operator	Description
+	Union. This binary operation returns a set consisting of the records that belong to any of the two set operands.
-	Exclusion. This binary operation returns a set of the records that belong to the first but not the other of the two set operands. Also, when used as a unary operator, it returns the complement set.
*	Intersection. This binary operation returns a set consisting of the records that belong to both of the two set operands.
/	Symmetric difference (XOR). This binary operation returns a set consisting of the records that belong to either, but not both of the two set operands.

Examples	Results
sum( {1-$} Sales )	Returns sales for everything excluded by the selection.
sum( {$*BM01} Sales )	Returns sales for the intersection between the selection and bookmark BM01.
sum( {-($+BM01)} Sales )	Returns sales excluded by the selection and bookmark BM01.
Sum({$<Year={2009}>+1<Country={'Sweden'}>} Sales)	Returns sales for the year 2009 associated with the current selections and add the full set of data associated with the country Sweden across all years.

Modifiers begin and end with angle brackets, <> , help.qlik.set_modifiers ** check all the links in this page

Example	Result
sum({$<OrderDate = DeliveryDate>} Sales)	Returns the sales for the current selection where OrderDate = DeliveryDate.
sum({1<Region = {US}>} Sales)	Returns the sales for region US, disregarding the current selection.
sum({$<Region = >} Sales)	Returns the sales for the selection, but with the selection in Region removed.
sum({<Region = >} Sales)	Returns the same as the example above. When the set identifier to modify is omitted, the inherited state is assumed.
sum({$<Year={2000}, Region={“U*”}>} Sales)	Returns the sales for the current selection, but with new selections both in Year and in Region.

14.CALS (Client Access Licences) - learnallbi
CALs are acquired with the purchase of the QlikView Server and therefore are server based not client based.Since CALs come with the server, they are specific to the Server.
CALs types – Following types of CALs are available in QlikView

Named User CAL
Named user CAL is specific to the user or the machine. It cannot be shared, but user with this type of license can access any number of documents and for unlimited duration. This type of license is best suited for the developers and designers, creating the dashboard or dedicated users who need 24 * 7 access to the dashboarding application.

Document CAL
Document CAL associates user to the document instead of the QlikView Server. It allows user to access only one QlikView document.This type of license is good for the users who are interested in only one specific document.

Session CAL
Session CAL is not tied to a specific user or a machine. Session CALs allows a single user to access multiple document. Session CALs are used in a setup when there are large number of non-frequent users. Users can use the license as long as it is available. A Session CAL is locked for a minimum of 15 minutes, when used. If a specific user is not accessing a document for over 15 minutes then this CAL is available and can be used by other users.

Usage CAL
A Usage CAL gives a user the ability to initiate one session i.e. accessing one document per running 28-day period.This license is also not tied to a specific user. Usage CAL and Session CAL can be used in combination.

Leasing of QlikView License
QlikView developer/user can lease a license from the server so that the user can work offline for a period of 30 days. The QlikView client once connected to the server will obtain a Named User CAL. A user can lease a license from the Server by opening a qvw on the server from QlikView desktop.

15.Qlikview Services community

QlikView Management Service (QMS): The QlikView Management Service tasks. It also publishes an API that external applications can use to interact with the QlikView Server deployment.

QlikView Server (QVS): The QlikView Server service is the core engine of QlikView when deployed on a server. It loads the QVW documents into memory, performs the necessary calculations to present the correct results, and handles user and document memory allocation.

QlikView Directory Service Connector (DSC): The Directory Service Connector is the service that connects to different user repositories, such as Active Directory, LDAP, or even a custom database of users, to allow configuration of document level security.

QlikView Distribution Service (QDS): Without a publisher license, the Distribution Service is simply a reload engine, reloading QVW documents on a QlikView Server. With the Publisher license added, it becomes an independent service that can perform reloads anywhere, distribute documents to multiple locations, and can perform many more tasks.

QlikView Web Service/Settings Service (for IIS) (QVWS): QlikView supports deploying its web pages on IIS but also comes with its own web server service—QVWS. If you do deploy on IIS, the Settings Service acts like the QVWS (listening on the same ports) and communicates with IIS to configure the correct setting .

15.Alternate States in set Analysis

=sum({A<[Product Sub-Category] = $::[Product Sub-Category],Region = B::Region>}Sales) 

the A above expression means take the selections only from Alternate state A objects,[Product Sub-Category] = $::[Product Sub-Category] this means that take the selection from product sub-category selection also even though this is not in any alternate state and Region = B::Region means take selections from Region filter also even though its in alternate state B.

16.Architecture

17.Section Acc multi field

Section Access;

LOAD 
Upper([ACCESS]) AS [ACCESS],
Upper([DOMAIN]) AS [DOMAIN],
Upper(NTNAME) AS NTNAME,
Upper([NTNAME]) as [ZONE_NTNAME],
Upper([NTNAME]) as [COUNTRY_NTNAME],
FROM [path]
(ooxml, embedded labels, table is sheet_name);

Section application;

ZONE_SECURITY:
LOAD 
DISTINCT Upper([NTNAME]) as [ZONE_NTNAME],
Upper([DOMAIN]) AS [DOMAIN],
Upper([USER_TYPE]) AS [USER_TYPE],
if(isnull([ZONE_CODE]),'*',UPPER([ZONE_CODE])) as [ZONE_CODE]
FROM [path]
(ooxml, embedded labels, table is sheet_name);
COUNTRY_SECURITY:
LOAD 
DISTINCT Upper([NTNAME]) as [COUNTRY_NTNAME], if(isnull([COUNTRY_CODE]),'*',UPPER([COUNTRY_CODE])) as [COUNTRY_CODE]
FROM [path]
(ooxml, embedded labels, table is sheet_name);