How To / Missing Manual

The Three-tier Architecture includes three layers in order to extract the data from the various database and store data in the qlikview data file, apply the business logic and develop the data model using QVD files and finally create the dashboard by using the second layer as a binary load which helps the business user to analyse and process the data.

  1. Load
(Extract Layer and QVD Layer)

2. Transform
(  QlikMart Layer)

3. Presentation (Presentation Layer)

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)

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

Remember

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

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

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString & MinString - How to + examples

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

Not authorized to view the specified document 8150

How to use- Dual()

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)

Bullet points:

• MaxString and MinString belongs to Qlikview String Aggregation function.
• They are equivalent to MAX and MIN for integers
• They return the highest and lowest string value

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 (A-z)  ‘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()) ) MaxString( 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

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

😎 With Aggr()

For each Country return lowest and highest Category string value.

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

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

• Mapping Table must have only two columns
• The fields/tables to be replaced (or map) must be loaded after the map..using statement

Data Model (Example 1)

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?

How To /Missing Manual(20+ articles)

This example has been produced to accompany the blog post QlikView Buttons, When, Why and How.

The shared QlikView demonstrates how actions can be attached a number of different types of objects and used to navigate and enrich a QlikView application. Download it and click around to see what you can find.

The blog article gives more information on Actions in QlikView, along with step by step instructions on how to implement some of what is displayed in this shared QlikView and a video walk through.

Please also see my other Community uploads here stevedarkand also listed on our website here https://www.quickintelligence.co.uk/examples/

Steve Dark

http://www.quickintelligence.co.uk/

    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/infographics-in-qlikview-vol-2/

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

Help with QV Function Survey‌   

you can find a practical way of using them.

Feeling Qlikngry?

How To /Missing Manual(19 articles)

    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:

• Sorting on y-values in charts or sorting by expression columns in straight tables is not allowed when Chart Inter Record functions are used in any of the chart's expressions. These sort alternatives are therefore automatically disabled.
• Suppression of zero values is automatically disabled when this functions is used

Data Model

(Copy and Pasted below code into Edit Script window and reload)

LOAD * inline

[

]

Now create Straight table and  remember to sort dimension and expression by load order

Returns values from position specified by second criteria (elem_no)

• Works only with distinct value
• field_name must be given as a string value (enclosed with single quotes ('))
• If elem_no is larger than the number of field values, NULL is returned.

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.

Hi,

The Following blog was inspired by a recent post that allowed me to exercise my brain into trying something new

(link to thread: Qlikview Chart)

Below is one of the two charts to be achieved in Qlikview. This led / music equalizer style looked pretty cool so I had to try

I finished with a chart like this. Close enough I'd say.

You can also adjust the number of bands you wish to see on the chart, but make sure you adjust the percentage scales for background colors

How did we achieve this chart?

The Idea is to break each value into multiple segments and then use background expression to make only alternate segments visible.

Simple isn't it.

So we've basically turned a simple single dimension one expression chart into a Stacked Chart with two Dimensions and one Expression.

Apply the same approach with Grid Charts which allow other visualization possibilities.

These are just for your viewing pleasure, hope you enjoy playing with them as much as I did.

Happy Qlik'in

Cheers

Vineeth

‌

Vineeth Pujari

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/qlikview-data-structures/

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/qlikview-examples/

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

http://www.quickintelligence.co.uk/qlikview-blog/

    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 Inter-Record functions and can only be used in the script (back-end).

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 row-no argument.

• If no row is stated, -1 is assumed.
• you can reference field that was not been previously loaded (created on the with AS alias)(See "Bonus 2" example)
• From the Help description "Fieldname must be given as a string (e.g. a quoted literal)."

          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?

How To /Missing Manual(17 articles)

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

How to use - Dimensionality()

What it is used for?

Returns the number of dimension pivot table rows that have non-aggregation 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

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 - secondarydimensionality.xlsx

Directory;

LOAD Product,

     Category,

     Type,

     Sales

FROM

secondarydimensionality.xlsx

(ooxml, embedded labels, table is Sheet1);

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()

Not authorized to view the specified document 8150

How to use- Dual()

Expression Scene Investigation, E1: Moving Average

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:

PROBLEM OF DIFFERENCE OF SUM

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()

How to use - MissingCount()

How to use- Dual()

Expression Scene Investigation, E1: Moving Average

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?

How To /Missing Manual(25 articles)

    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 Inter-Record 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 [

]

Now create a straight table and  remember to sort dimension and expression by load order

Returns position from value specified by second criteria (value)

• Returns positions of value being searched.
• field_name must be given as a string value (enclosed with single quotes ('))
• If value does not exist within column, 0 is returned

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?

How To /Missing Manual(20+ articles)

Hi All,

This is the collection of Incremental Load in Qlikview.

Hope this will help you.

Thank you.

Incremental Load.docx

Script for Incremental Loading of Multiple QVDs from a List of Table Names

Incremental Load Scenarios.pdf

Incremental Load.qvw

Incremental_Load.qvw

Incremental QVD script

Incremental Load @ Qlikview

incremental load issue while executing

Incremental Load.ppt

Different Ways to load data in qlikview

Sample for Incremental load

example for incremental load

Partial Reload Easy and Simple

LOAD data into QlikView

Incremental Load

Incremental Load

Re: Incremental Load

Switch Between Full Load And Incremental Load‌

Re: Incremental Load in Script

Incremental load

Re: incremental load.

Concatenate Incremental logic for Deleted records

Getting Input while reloading in Qlikview

Incremental load

Incremental load in easy steps

The native  QV gauges are not very sexy but you can give them little makeover.

Please see below one on how to do that

(you can find out more her

Dislike gauge charts? You may want to give them a second chance)

The idea here is to have our scale started at 12 am (or pm).

We will need two gauges precisely layout out one on another.

The first gauge is just a circle,  the second will hold calculation and segments.

Advice:

Turn on the " Design grid" option.

It is very helpful when deciding upon the size of the chart or adjusting the layout.

First Gauge

Press right button anywhere on the tab and from option choose "Create New Sheet Object.." and then "Chart..."

a) Tab : General :-

Choose gauge from the chart options.

b) Dimension:

Skip

c) Expressions:

Add '1' as expression

d) Sort:

Skip

e) Style:

Choose the first top left corner chart

f) Presentation:

Fallow the picture below:

g) Actions:

Skip

h) Colors:

The  "Color Background" and the "Frame Background " "Transparency" must be set to 100%

i) Layout:

The "Use Borders " box must be empty

j) Caption:

The "Show Caption" box must be empty

The final result is a  blue circle.

Second gauge

Points a-e

(Follow instruction on first chart)

f) Presentation:

Follow the picture below.

Where Segment 1 Lower Bound is 0  and color is Blue and Segment 2 Lower Bound is is your expression

(in this example only decimal number is use but normally you will need to enter an expression in form x/y)

and the color is transparent.

Add Text in "Text in Chart"  option and format it as %.

Segment 1	Segment 2

Points g-j

Follow instruction for the first chart.

The final result:

And the two gauges joined together:

and a small visualization

----------------------------

Did You like it?

Feeling Qlikngry?

How To /Missing Manual(18 articles)

Step by Step Guide how to Show/Hide Zero Values using buttons.

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 & 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()

Missing Manual - Top() and Bottom()