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 one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column........

• Returns value from  previous (Above) ornext (Below) row(s)
• Returns NULL for the first or last row.
• Equivalent to before()and after()for columns in Pivot Table.

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:

• First row is NULL
• Rows 2 and 3 are averages of values from two rows above current one (first we are going 1 row above and then adding 2 rows)

      sum(Sales)                          rangeavg (Below(sum(Sales),1,2))

For each group in right table:

• Rows 1 and 2 are averages of values from two rows below current one (first we are going 1 row above and then adding 2 rows)
• Last row is NULL

Still feeling hungry?

How to use - Only()

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

Missing Manual - Below() and Above()

How to use- Dual()

Missing Manual - Before() and After()

How to use - MissingCount()

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:

Remember

• To use the script below the Excel file must be saved in the same folder that your qvd file

        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?

How To /Missing Manual(18 articles)

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?

• dual - this is not actual part of the function but  prefix used to indicate what kind of value this function returns –in this case it means that this function can return numbers and characters
• Lookup - function name

and four arguments:

• field_name – is the name of a field in the other table – in the table where we want the value to take from
• match_field_name- is the name of a field in the other table – this is the matching value to the next argument

                                                                        <---->

• match_field_value - is the name of field in the this table-matching with previous argument
• [, table_name] - This is name of the other table (this argument is optional if omitted current table is used)

Preparation:

We have got two tables in Excel file.

For convenience let’s call them ‘Table1’ and ‘Table2’:

Table1	Table2

Remember

• To use the script below the Excel file must be saved in the same folder that your qvd file

        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:

• First two arguments and the last one must be enclosed in apostrophes '' .
• You need to drop Table2 using: drop table statement or all fields from Table1 will be loaded, bringing unexpected results.

The final tables you should see are like this:

Pros:

• You can return any field from second table based on any matched field from first table -

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:

• Little bit confusing with the syntax
• Slower than Apply Map
• No ‘False’ return – If there is no match a Null is returned.

      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?

How To /Missing Manual(17 articles)