How to use- Dual()

robert_mika — Fri, 06 Mar 2015 14:07:44 GMT

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

Re: How to use- Dual()

mithilesh17 — Thu, 02 Apr 2015 06:54:21 GMT

liked the interpretation in terms of top Text layer ( Hamburger ) and bottom Number layer ( salad ). Queries clarified instantly. very good post!

Re: How to use- Dual()

ziadm — Mon, 06 Apr 2015 03:51:37 GMT

This is a very powerfull function that I think that would solve many problems

Thanks

Re: How to use- Dual()

Anonymous — Thu, 21 May 2015 15:15:23 GMT

Thanks. What about performance, which case uses less memory and is faster for calculations: two fields day_name and day_id or one dual field dayOfWeek?

Re: How to use- Dual()

Mon, 19 Jun 2017 16:41:14 GMT

thanks!

Re: How to use- Dual()

robert_mika — Tue, 20 Jun 2017 20:31:23 GMT

You are welcome

Re: How to use- Dual()

Anonymous — Thu, 22 Jun 2017 08:47:26 GMT

it's very useful

Re: How to use- Dual()

techvarun — Mon, 07 Aug 2017 06:22:48 GMT

One of the best function of qlikview especially when dealing with Automation or Replication of reports.

Thanks for the beautiful representation

Re: How to use- Dual()

mjtaft2017 — Thu, 16 May 2019 12:13:34 GMT

perfect explanation - works for qlik sense also. I love the way you break it down and provide examples. Very helpful indeed.

article How to use- Dual() in Member Articles

How to use- Dual()

Re: How to use- Dual()

Re: How to use- Dual()

Re: How to use- Dual()

Re: How to use- Dual()

Re: How to use- Dual()

Re: How to use- Dual()

Re: How to use- Dual()

Re: How to use- Dual()