Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Missing Manual - GetFieldSelections() + Bonus Example
MaxString & MinString - How to + examples
The second dimension... or how to use secondarydimensionality()
