Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This forum is where any loggedin member can create a knowledge article.
How To / Missing Manual
Did You like it?
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
Below() and Above()
Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the current row within a column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent (Actually all QlikView charts have a straight table equivalent with the exception of the pivot table which has a more complex structure.).
On the first row of a column segment a NULL value will be returned, as there is no row above this one.
If the chart is onedimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column........
Syntax:
TOTAL[<fld{,fld}>]]expr  expression
offset  if greater that 0 will move the evaluation of expression to rows
further down or above
count  this parameter will only works with Chart Range Function (like RangeSum), it will specify the numbers of rows to be taken for calculations.(Please see last example)
Data Model:
(Copy and Pasted below code into Edit Script window and reload)
LOAD * inline
[
Year ,Month ,Sales
2015 ,January, 10
2015, February,20
2015 ,March ,30
2014 ,January ,10
2014 ,February,20
2014 ,March ,30
2013 ,January ,10
2013 ,February ,20
2013 ,March,30
]
Example 1:
Let's build a Straight Table with Year and Month as dimensions and expressions as below:
The left hand side shows use of sum(Sales) the right hand side result from our new expression.
sum( Sales ) > above(sum( Sales ) )
In each groups the first value is now NULL,the last values(30) have been omitted and the rest of the rows have been assigned value from one row below current row.
sum( Sales ) > below(sum( Sales ) )
In each groups the last value is now NULL,the first values (10) in each group have been omitted and the rest of the rows have been assigned value from one row below current row.
Example 2
By specifying the second criteria as 2 ,values in each group are shifted two rows up or down
sum( Sales )> below sum( Sales ), 2 )
sum( Sales )> above( sum( Sales ), 2 )
Example 3
above(TOTAL sum( Sales ) ) below(TOTAL sum( Sales ) )
Adding TOTAL before Sum will result with the first or last value to be omitted and the calculation to be shifted one row down or up.
Example 4
rangeavg (Above(sum(Sales),1,2))
rangeavg (Below(sum(Sales),1,2))
RangeAvg() takes 3 parameters
expression > Above/Below(sum(Sales),
offset of rows>1
number of rows to sum>2
sum(Sales) rangeavg (Above(sum(Sales),1,2))
For each group in right table:
sum(Sales) rangeavg (Below(sum(Sales),1,2))
For each group in right table:
Still feeling hungry?
Do you Qualify? How to use QUALIFY statement
Missing Manual  GetFieldSelections() + Bonus Example
MaxString & MinString  How to + examples
The second dimension... or how to use secondarydimensionality()
Missing Manual  Below() and Above()
What it is used for?
The Qualify statement is used for switching on the qualification of field names, i.e. field names will get the table name as a prefix.’
In situation where we have two (or more) Tables with the same field names:
Product  Payment 

The Qualify statement will assign name of the Table to fields:
Otherwise you will need to make changes the this path  [Qualify.xlsx]
QUALIFY *;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);
QUALIFY *;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);
The Outcome:
Table Viewer:
Read only if you need to know more....
If we have not used ‘QUALIFY’ statement in situation as above Qlikview would map both of the Tables with outcome like below:
The end result merge of those two Tables is correct only for the “Serial No” fields
The “Value” and “Category” fields although merged is none of use to us.
To fix this we can only Qualify fields that we do not want to associated:
QUALIFY Category,
Value;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);
QUALIFY Category,
Value;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);
or by using UNQUALIFY statement:
(which works as opposite to QUALIFY)
QUALIFY *;
UNQUALIFY [Serial No];
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);
QUALIFY *;
UNQUALIFY [Serial No];
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);
In second example the ‘QUALIFY *’ part will add Table name to all fields and the UNQUALIFY statement will remove those names only from specified fields(this method is very useful if we got large numbers of fields)
Outcome is as below:
Fields:
Table Viewer:
Feeling Qlikngry?
This example QlikView document shows how to create a bar chart that shows variance to target, both as an absolute value and as a percentage.
The charts produced look like this:
By showing both these charts you can see both how regions are comparing to each other and against the targets that have been set for them.
The example was created to accompany the Quick Intelligence blog post, that can be read here:
https://www.quickintelligence.co.uk/qliktargetbarchart/
The example is also available on Qlik Community as a Qlik Sense application.
A list of all our downloadable example files can be found here:
https://www.quickintelligence.co.uk/examples/
Hope you find this application useful.
Regards,
Steve
Definition:
Returns the number of dimension columns that have nonaggregation content. i.e. do not contain partial sums or collapsed aggregates.
A typical use is in attribute expressions, when you want to apply different cell formatting depending on aggregation level of data.
This function is only available in charts. For all chart types except pivot table it will return the number of dimensions in all rows except the total, which will be 0.
What does it mean?
We have Table with 4 dimensions(columns): Product,Category,Type,Sales
Now we want to create Pivot Table by using those Dimensions.
We are going to use only 3 of them(Product,Category,Type) and use 4th(Sales) in our expression.
The result is shown below:
This Pivot Table has 3 dimensions so its maximum dimensionality is 3.
For better understating please see table below.
The function is used to show on which dimensionality level each of the Pivot Table row is:
'Sugar' has dimensionality of 1 which is Total for that 'Product'.
'Salt' has dimensionality of 2 which is Total for each 'Category' of that 'Product'.
'Oil' has dimensionality of 3 which is single value for each 'Type' of the 'Product's' 'Category'.
So then more Dimension we use the greater dimensionality of our Pivot Table is.
Practical use:
1) To show the level of dimensionality:
Expression:
if(Dimensionality()=1 ,RGB(151,255,255),if(Dimensionality()=2 ,RGB(0,238,0),if(Dimensionality()=3,RGB(255,130,171))))
2) Highlight background of rows which on each level fall into certain condition:
Expression:
if(Dimensionality()=1 and sum(Sales)<150,RGB(151,255,255),if(Dimensionality()=2 and sum(Sales)<=20,RGB(0,238,0),if(Dimensionality()=3 and Sum(Sales)<=20,RGB(255,130,171))))
LEVEL1 > Values <140  LEVEL 2 > Values <=20  LEVEL 3 > Values <=20 

Otherwise you will need to make changes the this path  [Dimensionality.xlsx]
Directory;
LOAD Product,
Category,
Type,
Sales
FROM
[Dimensionality.xlsx]
(ooxml, embedded labels, table is Sheet1);
Felling Qlingry?
Have you ever wonder how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
Dual()
Definition:
Forced association of an arbitrary string representation s with a given number representation x. In QlikView, when several data items read into one field have different string representations but the same valid number representation, they will all share the first string representation encountered. The dual function is typically used early in the script, before other data is read into the field concerned, in order to create that first string representation, which will be shown in list boxes etc.
Syntax:
where
text  this is first column of data
number this is second column of data
Qlikview has specific way of storing data. Each field is represented by Text and Number data type called dual.
The Text format is visible to user and the Number is being used for calculation and sorting.
You can imagine that each field has top Text layer and bottom
(like salad in your Hamburger) Number layer.
This association works most of the time and for most of the data type but there are situation that we need to force this association to work the way we want.
Preparation
Qlik help example contains two tables: inline and from csv file(sample attached)
load dual ( string,numrep ) as DayOfWeek inline
[ string,numrep
Monday,0
Tuesday,1
Wednesday,2
Thursday,3
Friday,4
Saturday,5
Sunday,6 ];
Directory;
LOAD Date,
Sales
FROM
afile.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
//The last line is not part of Qlikview help but you have to have this line to correctly load data into Qlikview
after Reload we have:
As you probably know when Values are on the left hand side they represent Text when on the right Number.
So why we have Text on the right?
In the background (during load) the numbers have been associated with the text.
So what you see is the bun what you do not is the underlay salad:
Practical use:
Example 1
The simple but maybe not the most useful calculation is to add Days of the week from our data =sum(DayOfWeek) 
Example 2
Counting days of the week:
Example 3
(please use attached xlsx file and below Script)
A survey has been conducted and the data in Excel file looks like this:
What we want is to assign more meaningful description to the rate numbers.
Script:
Data:
mapping load * Inline
[ Rate,Value
Very Good,5
Good,4
Average,3
Bad,2
Very Bad,1
]
;
Sort:
load
Responds,
dual(Rate,ApplyMap('Data',Rate)) as Rate
FROM
Survey.xlsx
(ooxml, embedded labels, table is Sheet1);
Now we can create Straight Table or bar Bar Chart:
This function can be use in many different ways.Please research our community to find more examples.
Still feeling hungry?
Do you Qualify? How to use QUALIFY statement
Missing Manual  GetFieldSelections() + Bonus Example
MaxString & MinString  How to + examples
The second dimension... or how to use secondarydimensionality()
What it is used for?
To return values from another table field based on matching field value from current table.
Typically used when only one field records from another table are needed.
(You are dropping this (second) table later on.(see below) )
How does it look like?
How is it design?
and four arguments:
<>
Preparation:
We have got two tables in Excel file.
For convenience let’s call them ‘Table1’ and ‘Table2’:
Table1  Table2 

Remember
Otherwise you will need to make changes the this path  [LookUp.xlsx]
Table2:
LOAD Category,
Sales,
Country
FROM
[Lookup.xlsx]
(ooxml, embedded labels, table is Table2);
Table1:
LOAD Category,
Lookup('Country','Category',Category,'Table2') as Country
FROM
[Lookup.xlsx]
(ooxml, embedded labels, table is Table1);
drop table Table2;
Remember to... Look Up!
(This is where the name of this function has another meaning)
Table 2 must be above Table1!
or after Reload you will get script error.
How does it work?
Formula:
Script:
Table2:
LOAD Category,
Sales,
Country
FROM
[Lookup.xls]
(ooxml, embedded labels, table is Table2);
Table1:
LOAD Category,
Lookup('Country','Category',Category,'Table2') as Country
FROM
[Lookup.xlsx]
(ooxml, embedded labels, table is Table1);
drop table Table2;
Remember:
The final tables you should see are like this:
Pros:
You can look at first field in the table to return third one and to look at third field to return the first one.
Cons:
To overcome this drawback we can wrap the formula in IF function:
If(isnull(Lookup('Country','Category',Category,'Table2')),'No match',Lookup('Country','Category',Category,'Table2')) As Country
Exercise :
Return field ‘Sales’ from Table2 by using ‘Category’ field from Table1.
(Answer at the end of this article)
Appendix:
For many of you coming from Excel world and working with VLOOKUP this syntax is little difficult to understand at first.
Why we can not have:
Take value from this table go to the another table matched the field and return what we need?
So the syntax would have been:
Lookup(Category,Table2,Category,Country)
Would this not be simpler?
This is open question so please state your case....
Answer to Exercise:
Lookup('Sales','Category',Category,'Table2') as Sales
Still feeling hungry?
Bullet points:
What actually a value of a string?
This is the value described by ANSI character standard where 0 ( NULL) is equal to 0 and ÿ to 255.
(For full ANSI character set please attached dosument)
Taking only the standard English alphabet (Az) ‘A’ will be the MIN and ‘z’ will be the MAX.
Remember:
In ANSI standard A < a and Z < z
(A=65,Z=90,a=97,z=122)
Language specific characters will be not recognized in the place where they are in your alphabet but they will be 'pushed' to the end
(see ANSI table)
MinString and MaxString works on dimensions (columns) only so you cannot use is to evaluate single character or strings .
(MaxString(‘a’,’z’…) is not supported
Examples:
1) MIN & MAX (textbox)
Data  Expression  Results 

MinString(Category) MaxString(Category) 
2) MIN and MAX of String per group (Straight table)
Data  Expression  Result 

MinString(Category) MaxString(Category) 
3) With condition:
Return Max and Min string based on another column
We are looking for MIN and MAX string value from column Category where Country='Poland'
Data  Expression  Solution  Result 

MinString( if(Country='Poland', Category, null()) )

4) With LEFT/RIGHT
When used with LEFT the result will be the same as without as the value will be still based on first characters.
Data  Expression  Solution  Result 

=MinString( Right(Country,1)) =MaxString( Right(Country,1)) 
5) With MID
Data  Expression  Solution  Result 

=MinString( Mid(Country,2,2)) =MaxString( Mid(Country,2,2)) 
6) With Substring
When our data are little scrambled TextBetween will return string between '.' and end of the row.
Data  Expression  Result 

=MinString(TextBetween(Country,'.','',1)) =MaxString(TextBetween(Country,'.','',1)) 
7) In set analysis
When Criteria of Sales is 2 return Country with highest and lowest string Value
Data  Expression  Result 

=MinString({<Sales={2}>} Country)

😎 With Aggr()
For each Country return lowest and highest Category string value.
Data  Expression  Result 

=Aggr(Minstring(Category),Country) =Aggr(Maxstring(Category),Country) 
If know about other ways of using these functions please let me know.
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
Before () and After()
Description
Returns the value of expression evaluated with a pivot table's dimension values as they appear in the column before the current column within a row segment in the pivot table. This function returns NULL in all chart types except pivot tables.
On the first column of a row segment a NULL value will be returned, as there is no column before this one.
If the table is onedimensional or if the expression is preceded by the total qualifier, the current row segment is always equal to the entire row.
If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter field sort order. The inter field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom.
Specifying an offset greater than 1 lets you move the evaluation of expression to columns further to the left of the current column. A negative offset number will actually make the before function equivalent to a after function with the corresponding positive offset number. Specifying an offset of 0 will evaluate the expression on the current column. Recursive calls will return NULL.
By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of n table columns counting to the left from the original cell. In this form, the function can be used as an argument to any of the special Chart Range Functions.
Data Model:
(Copy and Pasted below code into Edit Script window and reload)
LOAD * inline
[
Product ,Category ,Sales
Salt ,White, 30
Salt, Brown ,40
Salt ,Red ,30
Sugar ,White ,10
Sugar ,Brown ,20
Sugar ,Red ,20
Wine ,White ,40
Wine ,Brown ,30
Wine ,Red ,10
]
Example 1:
Let's build a Pivot Tables with Product as dimension and expressions as below:
The left hand side shows an ordinary use of Sales the right hand side result from our new expression.
sum( Sales ) > before(sum( Sales ) )
Salt value has been assigned to Sugar,
Sugar to Wine,
Salt is now NULL .(there is no column before this one)
sum( Sales ) > after(sum( Sales ) )
Here we see the opposite result:
Sugar value has been assigned to Salt,
Wine to Sugar
and Wine is NULL (no column after this one)
Example 2
Specifying the second criteria as 2 we shifted all values by 2 columns right or left.
before( sum( Sales ), 2 )
after( sum( Sales ), 2 )
Example 3
before(total sum( Sales ) )
after(total sum( Sales ) )
When using with one dimension this expression returns values as if we have used ordinary before/after (Sum(Sales))
For more than one dimension the return value will be based on last column of the first(top) dimension and then appropriate columns for the second dimension.
Salt and Sugar have been omitted leaving Wine as the last column
Example 4
rangesum (Before(sum(Sales),1,2))
RangeSum() takes 3 parameters
expression > Before/After(sum(Sales),
offset of columns>1
number of columns to sum>2
sum(Sales) rangesum (Before(sum(Sales),1,2))
In our example we are shifting our calculation one column to the right:
Salt  As there is no column before Salt this column has been ignored
Sugar we are shifting our calculation one column to the left (Salt) and are summing previous two columns.
As there is no column before Salt the final result is value of Salt alone.
Wine by shifting calculations one column to the left(Sugar) and summing two previous columns we getting 100+50=150
sum(Sales) rangesum (after(sum(Sales),1,2)
In this example we are shifting our calculation one column to the right:
Wine  As there is no column after Salt this column has been ignored
Sugar we are shifting our calculation one column to the right(Wine) and aresumming previous two columns.
As there is no column after Wine the final result is value of Wine alone.
Salt by shifting calculations one column to the right(Sugar) and summing two previous columns we getting 50+80=130
Still feeling hungry?
Do you Qualify? How to use QUALIFY statement
Missing Manual  GetFieldSelections() + Bonus Example
MaxString & MinString  How to + examples
The second dimension... or how to use secondarydimensionality()
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
Map...using
or some kind of a lookup...
In most of the cases we want to keep current field and by mapping corresponding values create new Field.
(Don't join  use Applymap instead)
But sometimes we want to replace data with new one.
Here is where map...using comes in handy..
Description(Qlikview Help)
The map ... using statement is used for mapping a certain field value or expression to the values of a specific mapping table. The mapping table is created through the Mapping statement.
The automatic mapping is done for fields loaded after the map ... using statement until the end of the script or until an unmap statement is encountered.
The mapping is done last in the chain of events leading up to the field being stored in the internal table in QlikView. This means that mapping is not done every time a field name is encountered as part of an expression, but rather when the value is stored under the field name in the internal table. If mapping on the expression level is required, the Applymap() function has to be used instead.
Syntax:
*Fieldlist  is name of the field which value we will be look for
mapname  us the table from where the data will be picked
Replacing matching fields values with corresponding values from the Mapping Table
Data Model (Example 1)
Header 1  Header 2 

(Copy and Pasted below code into Edit Script window and reload) Cmap: MAPPING LOAD * INLINE [ Code,Country PL,Poland UK,United Kindgdom DE,Germany ]; Map Country Using Cmap; Data: LOAD * INLINE [ Country PL GB DE FR ]; 
The outcome is as below:
PL  have been replaced by Poland
DE  have been replaced by Germany
FR and GB  does not have a match in Cmap table
Data Model (Example 2 & 3)
X:
MAPPING LOAD * INLINE [
Code,Country
PL,Poland
UK,United Kindgdom
DE,Germany
];
map A, B, C using X;
Data:
LOAD * INLINE [
A, B ,C
PL, GB,DE
GB, PL,FR
DE, FR,PL
FR, DE,GB
];
Outcome:
In this example matching values from all three fields have been replaced by corresponding values.
The A,B,C field names can replaced by * (Example 3) to get the same outcome
map * using X;
If you wonder where this statement may be useful , think of a model with more than one table where values have to be replaced
In below example all fields that have been stated as map criteria (A,B,C,G  no matter which table ) have been replaced by corresponding values:
Header 1  Header 2 

X: MAPPING LOAD * INLINE [ Code,Country PL,Poland UK,United Kindgdom DE,Germany ]; map A,B,C,G using X; Data: LOAD * INLINE [ A, B ,C PL, GB,DE GB, PL,FR DE, FR,PL FR, DE,GB ]; Data2: LOAD * INLINE [ E, F ,G PL, GB,DE GB, PL,FR DE, FR,PL FR, DE,GB ]; 
Feeling Qlikngry?
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
Valueloop() & VaueList()
Both of those functions belong to Synthetic Dimension Functions.
Synthetic Dimension is a type of Calculated Dimension
 the difference between "standard" Calculated Dimension and Synthetic one is that
the standard dimensions are based on values from existing fields
whereas for Synthetic Dimensions those values are created "on the fly".
The drawback here is that you can not mix standard dimensions with synthetic in the way you would expect.
(please see "Practical use").
ValueLoop()
Used:
Back End No
Front End  Yes
Description(Qlikview Help)
Returns a set of iterated values which, when used in a calculated dimension, will form a synthetic dimension.
The values generated will start with the from value and end with the to value including intermediate values in increments of step. In charts with a synthetic dimension created with the valueloop function it is possible to reference the dimension value corresponding to a specific expression cell by restating the valuloop function with the same parameters in the chart expression. The function may of course be used anywhere in the layout, but apart from when used for synthetic dimensions it will only be meaningful inside an aggregation function
Create series of numbers in a range given by criteria.
from  first value
to  last value
step  intermediate values criteria.
When step is missing 1 is assumed
Qlikview help examples are very straightforwards and easy to understand:
Example 1  Example 2  Example 3 

valueloop ( 1, 3 ) From 1 to 3, (step is omitted so 1 is assumed):  valueloop ( 1, 5, 2 ) From 1 to 5, step 2: 1,1+2=3,3+2=5  valueloop ( 11 ) returns the value 11 
Practical use
Example 1
If you need to provide calculations to check if the MOD of values from 0 to 100 with step 5 is divided by 10 without remainder
Create Dimension: ValueLoop(0,100,5)
and Expression: if(mod(ValueLoop(0,100,5),10)=0,'OK', 'No OK')
See also:
How to create a Square Pie Chart
or
qlikfreak.wordpress.com/2014/06/17/infographicsinqlikviewvol2/
ValueList()
Used:
Back End No
Front End  Yes
Description(Qlikview Help)
Returns a set of listed values which, when used in a calculated dimension,
will form a synthetic dimension. In charts with a synthetic dimension created with the valuelist function it is possible to reference the dimension value corresponding to a specific expression cell by restating the valuelist function with the same parameters in the chart expression. The function may of course be used anywhere in the layout, but apart from when used for synthetic dimensions it will only be meaningful inside an aggregation function
Create series of values from given list
v1  list of values
Again, both of those examples are very easy to understand:
Header 1  Header 2 

valuelist ( 1, 10, 100 ) 
Header 1  Header 2 

valuelist ( 'a', 'xyz', 55 ) 
Practical use
Data Model
LOAD Date,
Year(Date) as Year,
Values
inline [
Date, Values
01/01/2009, 1
11/04/2009, 2
20/07/2009, 2
28/10/2009, 2
05/02/2010, 2
16/05/2010, 2
24/08/2010, 1
02/12/2010, 1
]
The usual way of creating straight table is be to add Year as Dimension and sum(Values) as expression.
This will return value for each Year.
But if we want o use Synthetic Dimension in the same way this will return only one Total value for both years.
One of the way to use this function is to create list (similar to Statistics Box) with your own KPI's:
and then use nested IF statement to create your Metrics
=if(ValueList('Sum','Count','Average')='Sum',Sum(Values),
if(ValueList('Sum','Count','Average')='Count',Count(Values),Avg(Values)))
to get below result:
As nesting IF's can be tricky and cumbersome beyond 2 or 3 criteria we can use Pick/Match functions to improve our calculations:
=pick(match(ValueList('Sum','Count','Average'),'Sum','Count','Average')
,Sum(Values),Count(Values),Avg(Values))
Conclusion:
Although both of those function are not very often used
(they did not make to final 30 of rwunderlich survey
you can find a practical way of using them.
Feeling Qlikngry?
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
FieldValue()
This function belongs to Inter Record Functios and has few rules:
Data Model
(Copy and Pasted below code into Edit Script window and reload)
LOAD * inline
[
HelpText,  Name 
Block,  Bob 
Color,  John 
Topics,  Jenny 
Font,  Curt 
Data,  Martin 
Group,  Mirek 
Total,  Stan 
]
Now create Straight table and remember to sort dimension and expression by load order
Returns values from position specified by second criteria (elem_no)
Syntax:
Field_name  is a name of of your field(column)
elem_no  position from the top
Example
fieldvalue( 'Helptext', 5 )
Bonus
By using:
=FieldValue('Name', RowNo()+1)
=FieldValue('Name', RowNo()1)
you can shift values one row below/or above current one loosing the first or last value.
QlikView creates a number of log files and XML files. From project folders through to reload logs and QMC schedules. Perhaps unsurprisingly the best tool to consume these files is QlikView itself.
This application loads the structure files that QlikView allows you to extract based on the data model of the currently open app. It then gives a few simple ways of viewing that information.
There is an accompanying blog post describing the ways that you can use the structure files created by QlikView, and apps such as this to quickly get a handle on a new data source and work out a plan of attack to analyse it.
This can be found here:
http://www.quickintelligence.co.uk/qlikviewdatastructures/
There is also a list of the other apps that I have made available on Qlik Community on the site, here:
http://www.quickintelligence.co.uk/qlikviewexamples/
If you have any questions about the application, or suggestions on how it could be improved, please leave these below or in the comments on the blog post itself.
Steve
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
Peek()
not a boo...
This function belongs to to InterRecord functions and can only be used in the script (backend).
Description(Qlikview Help)
Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.
Fieldname must be given as a string (e.g. a quoted literal).
Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. 1 denotes the last record read.
If no row is stated, 1 is assumed.
Tablename is a table label without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.
Syntax:
Field_name  is a name of of your field(column)
row_no  the row from which the data is returned
(0 is first row
1 is second row
and so on..
1 is the last row)
table_name  a name of table from where the data are fetched
Returns values from previous row or row specified by the rowno argument.
This is true only if we use this function to create variable (please see below examples)
Data Model
(Copy and Pasted below code into Edit Script window and reload)
Tab1:
load
peek(Sales) as S1,
peek( Sales,2 ) as S2,
peek( Sales,2 ) as S3,
if(rowno()=1,Sales,Peek(RunnigTotal,1)+Sales) as RunnigTotal,
Previous(Sales) as PSales,
numsum( Sales, peek( 'Sales' ) ) as Bsum,
Sales
inline [
Sales
100
200
300
400
]
;
load
peek( Sales, 0, 'Tab1' )as S4
resident Tab1;
LET vpeek= peek( 'Sales', 1, 'Tab1' ) ;
Please see below swuehl comments about difference when you use negative or positive numbers as second argument.
Is worth to mention that Peek() is reading from Output table(opposite to Previous() function which reads from Input Table.
Example:
OutputTable:
LOAD field
RESIDENT InputTable;
source:Difference between peek() and previous() funcation
Example 1
In this example as we did not specify the row_no argument, all but last rows are returned.
peek( 'Sales' )
peek( 'Sales' ) = peek( 'Sales',1)
Example 2
IF the row_no argument is added only ONE value is returned
peek( 'Sales',2 ) Remember 2 = third row
Example 3
If we use negatives numbers as row_no the return value is our initial table minus number of rows specified by the second argument.
peek( 'Sales',2 )
As you see the last 2 rows have been removed from the table.
Example 4
The below syntax is used when we want to return value from external table(see data model)
peek( 'Sales', 0, 'Tab1' ) as S4
Example 5
Each row is a SUM of current row + one row above.
numsum( Sales, peek( 'Sales' ) ) as Bsum
Bonus
How to store value into variable:
With below example
LET vpeek= peek( 'Sales', 1, 'Tab1' ) ;
we will store value of 400 in variable vpeek
and then use in front end development.
Bonus 2
As previously stated we can use Peek to return values from field that was not been yet created.
How does it wok?
In our data model we have this line:
if(rowno()=1,Sales,Peek(RunnigTotal,1)+Sales) as RunnigTotal,
Although RuningTotal has not yet been loaded we can return the values from that line:
Still feeling Qlikngry?
Prologue...
Firstly I would like to say that probably it would be easier to call this function Horizontal Dimensionality (or HDim or even SecDim)
This could clear some clouds  when you first see this function you may feel that this another level for dimensionality function.
(for those who wants to know more about the dimensionality function please see this document
What it is used for?
Returns the number of dimension pivot table rows that have nonaggregation content. i.e. do not contain partial sums or collapsed aggregates. This function is the equivalent of the dimensionality function for horizontal pivot table dimensions.
The secondarydimensionality() function always returns 0 when used outside of pivot tables.
What does it mean?
Where dimensionality shows number of vertical dimensions, secondarydimensionality shows number of horizontal dimensions.
Lets take our sample data.
We have four dimensions(columns):
Product,Category,Type and Sales.
Each Product have 2 Categories
Each Category can have up to 3 Types
Let's create Pivot Table using 3 of those Dimension Product,Category and Type and use Sales as our Expression.
This Pivot Table has 3 dimensions so its maximum dimensionality is 3.
'Sugar' has dimensionality of 1 which is Total for that 'Product'.
'Salt' has dimensionality of 2 which is Total for each 'Category' of that 'Product'.
'Oil' has dimensionality of 3 which is single value for each 'Type' of the 'Product's' 'Category'
So in few words : secondarydimensionality (and dimensionality) is the number of dimensions used in a Table
For better understating please see table below:
The function is used to show on which dimensionality level each of the Pivot Table row is:
Practical use:
1) To show the level of dimensionality:
Expression:
if(secondaryDimensionality()=1,RGB(151,255,255),if(secondaryDimensionality()=2,RGB(0,238,0),
if(secondaryDimensionality()=3,RGB(255,130,171))))
2) Highlight background of rows which on each level fall into certain condition:
Expression:
if(Dimensionality()=1 and sum(Sales)<150,RGB(151,255,255),if(Dimensionality()=2 and sum(Sales)<=20,RGB(0,238,0),if(Dimensionality()=3 and Sum(Sales)<=20,RGB(255,130,171))))
LEVEL 1 > Values <35  LEVEL 2 > Values <=15  LEVEL 3 > Values <=20 

Otherwise you will need to make changes the this path  secondarydimensionality.xlsx
Directory;
LOAD Product,
Category,
Type,
Sales
FROM
secondarydimensionality.xlsx
(ooxml, embedded labels, table is Sheet1);
Still feeling hungry?
Do you Qualify? How to use QUALIFY statement
Missing Manual  GetFieldSelections() + Bonus Example
MaxString & MinString  How to + examples
The second dimension... or how to use secondarydimensionality()
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
GetFieldSelections()
Definition:
Returns a string with the current selections in a field.
valueSep  is the separator to be put between field values. The default is ', '.
max_values  is the maximum number of field values to be individually listed.
When a larger number of values is selected the format 'x of y values' will be used instead.
The default is 6.
Preparation:
Script to play with:
Load * Inline
[
Year
2007
2008
2009
2010
2011
2012
2012
2013
2014
2015
2016
]
Example 1:
GetFieldSelections(Year )
Our field Year has 10 values (Years from 2007 to 2016)
The default separator is ',' (coma)
The maximum default numbers of values is 6
We used the same expression for all 3 sets of data
Left: Selection of 6 values
Middle: When more that 6 values the result will change to show the values which are not selected.
Right: Selecting whole sets of values will return 'ALL' as result
Data  Expression and Results 

Example 2: valueSep
We can assign here any character (or string).
Examples 3: max_values
The default values of 6 is overwritten by the value we have specified(10)
Interesting:
When playing with this function somehow I managed to get result like below
If you can replicate it please post it.
Bonus:
Practical use:
Still feeling hungry?
Do you Qualify? How to use QUALIFY statement
Missing Manual  GetFieldSelections() + Bonus Example
MaxString & MinString  How to + examples
The second dimension... or how to use secondarydimensionality()
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
Top() and Bottom()
Description
Returns the value of expression evaluated with the chart's dimension values as they appear on the last row of the current column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent.
If the chart is onedimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column.
If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order. The inter field sort order for pivot tables is defined simply by the order of the dimensions from left to right. For other chart types this can be manipulated in the Chart Properties: Sort dialog. ...
Syntax:
TOTAL[<fld{,fld}>]]expr  expression
offset  if greater that 0 will move the evaluation of expression to rows further down or above
count  this parameter will only works with Chart Range Function (like RangeSum), it will specify the numbers of rows to be taken for calculations.(Please see last example)
Data Model:
(Copy and Pasted below code into Edit Script window and reload)
LOAD * inline
[
Year ,Month ,Sales
2015 ,January, 10
2015, February,20
2015 ,March ,30
2014 ,January ,40
2014 ,February,50
2014 ,March ,60
2013 ,January ,70
2013 ,February ,80
2013 ,March,90
]
Let's build a Straight Table with Year and Month as dimensions and expressions as below:
Example 1:
The left hand side shows use of sum(Sales) the right hand side result from our new expression.
sum( Sales ) > bottom(sum( Sales ) )
For each row the returned value is the same as the bottom value for this group: 30,60.90
sum( Sales ) > top(sum( Sales ) )
For each row the returned value is the same as the bottom value for this group: 10,40,70.
Example 2
By specifying the second criteria as 2 , the returning value is the second value from the top or bottom of each group.
In this example top and bottom are giving the same result (the middle value) as we have 3 data points for each group
sum( Sales )> top sum( Sales ), 2 ) sum( Sales )> bottom( sum( Sales ), 2 )Example 3
top(TOTAL sum( Sales ) ) bottom(TOTAL sum( Sales ) )Adding TOTAL before Sum will result with the first or last value to be assigned to all rows.(Total will ignore dimension)
Example 4
RangeAvg() takes 3 parameters
expression > Top/Bottom(sum(Sales),
offset of rows>1
number of rows to sum>2
sum(Sales) rangeavg (Top(sum(Sales),1,2))
For each group in right table:
sum(Sales) rangeavg (Bottom(sum(Sales),1,2))
For each group in right table:
Still feeling hungry?
Do you Qualify? How to use QUALIFY statement
Missing Manual  GetFieldSelections() + Bonus Example
MaxString & MinString  How to + examples
The second dimension... or how to use secondarydimensionality()
Missing Manual  Below() and Above()
Working with Qlikview or QlikSense is like playing a good RPG or FPP game  you are working hard looking for a solution and then you
picking a "Magic Weapon" and you are on next level.
"Did you know...?" is all about quick tips and tricks
So let me start.
Did you know that...
Can the cyclic group be used in an expression?
Did you?
That's good but if you did not  you may be surprised.
Recently I had to find a way to pick up values from Cyclic Group only to discover that after
if(GerCurrentField(...
Qlikview throws beautiful Red Lines.
Head down...fists tight. but wait.
What is the Community for?
My quest leads me to this topic
where our Superhero johnw posted the solution to my problem.
Because QV does not recognize its own syntax you are left out without IntelliSense.
Try to enter long expression  and you will be sweating and swearing...
The good point is  Its Work.
For those who are using personal edition:(with all credits to John)  please try yourself
Script:
dimension:
LOAD * INLINE [
CategoryID, CategoryName
1, Car
2, CD
3, DVD
4, Laptop
5, Software
6, Sunglass
];
fact_sales:
LOAD * INLINE [
CategoryID, SaleValue
1, 1000
2, 40
3, 50
4, 200
5, 25
6, 15
];
fact_cost:
LOAD * INLINE [
CategoryID, CostValue
1, 700
2, 30
3, 25
4, 120
5, 5
6, 8
];
[Expression Group]:
LOAD * INLINE [
Sales, Cost
Sales, Cost
];
GUI:
1)
Create new bar chart
2)
Add dimensions:
a) CategoryName
and
b)Create new Cyclic dimension SalesCost with Cost and Sales as Dimensions.
3) Add Expression:
=if(getcurrentfield(SalesCost)='Sales',sum(SaleValue),sum(CostValue))
And Voila! you got your Magic Sword.
Good luck in your quests.
Feeling Qlikngry?
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
FieldIndex()
This function belongs to two groups Chart Inter Record Functions
and InterRecord functions.
Returns the position of the field value value in the field fieldname (by load order). If value cannot be found among the field values of the field fieldname, 0 is returned. Fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes.
Data Model
(Copy and paste below code into the edit script window and reload)
LOAD * inline [
Helptext,  Name 
Block,  Bob 
Color,  John Doe 
Topics,  Jenny 
Font,  Curt 
Data,  Martin 
Group,  Mirek 
Total,  Stan 
]
Now create a straight table and remember to sort dimension and expression by load order
Returns position from value specified by second criteria (value)
Syntax:
field_name  the name of your field/column
value  the value for which you want to know the position
Example
fieldIndex( 'Name', 'John Doe' )
Bonus
They said there is no lookup in Qlik front end?
Think again ...
=FieldValue('Helptext',FieldIndex('Name', 'John Doe'))
by wrapping FieldIndex with FieldValue we can return the unknown value of the 'HelpText' column that corresponds with the given value 'John Doe' of the Name column.
Feeling Qlikngry?
( based on a true story)
Cast:
SUM(),ABOVE(),RANGESUM()
Case:
Return Average of last 3 months(not including current one)
Preparation:
(Click on picture to see full size)
Create Straight Table (Layout>New Sheet Object>Chart...) >
Use:
Year and Month
as dimension >
and Sales as expression.
(In this example Sales and SUM(Sales) deliver the same results as we have unique values in Month dimension. >
Investigation:
The expression we are going to take apart is:
Rangeavg(Above(Sum(Sales),1,3))
Step 1) SUM()
The syntax of SUM in Qlikview is quite complicated when used in charts:
(We not going to investigate that now so please refer to Qlikview Help for more details)
For our example we only need:
SUM(Sales)
The result of this expression is:
Step 2) ABOVE()
ABOVE takes 3 parameters:
expression  which in our example is: SUM(Sales).(we are ignoring the total here)
offset this is integer value(0,1,2...) to specify how many rows the whole expression should be move down in relation to current row
n  this is integer value(1,2...) and will return range of values.
Remember:
The last parameter will only work when used as argument with another Chart Range Function
(please see Qlikview Help for details)
The result of wrapping SUM with ABOVE
ABOVE(SUM(Sales),offset)
is presented below.
Offset of 1,2 and 5 rows:
Step 3) Rangeavg()
This function returns average of values or expressions:
Rangeavg(1,1) =1
Rangeavg(1,2,3)=2
RangeAvg(sum(1+2),sum(2+3)) is equal to RangeAvg(3,5)=4
Step 4)
So what actually is going on behind the scene we we use:
Rangeavg(Above(Sum(Sales),1,3))?
We can rephrase that expression to:
" Sum values from dimension Sales then go 1 row above and take values from 3 rows going up"
So based on our example :
Will always return NULL
as we can not got above row 1 >
Row two:
We can only go 1 row up and return 1 value
Rangeavg(2000) = 2000
Row three:
"Go up 1 and return 3 values going up"
We can only go 2 rows up:
Rangeavg (2000,3000) =
(2000+3000)/2=2500
Row four:
"Go up 1 row..."
This is first row where values from 3 rows above
can be returned(Row 1, Row 2 ,Row 3)
Rangeavg(Row 1, Row 2 ,Row 3) = Rangeavg(2000+3000+1000)=6000/3=2000
Row five:
Rangeavg(Row 2, Row 3 ,Row 4)
=Rangeavg(3000+1000+2500)=6500/3=2167
and so on...
The final result:
Conclusions:
The offset parameter of the ABOVE function according to Qlikview Help: