Dual() combines a number and a string into a single record, such that the number representation of the record can be used for sorting and calculation purposes, while the string value can be used for display purposes.
Syntax:
Dual(text, number)
Return data type: dual
Information noteAll dual return values are right-aligned.
Arguments:
Arguments
Argument
Description
text
The string value to be used in combination with the number argument.
number
The number to be used in combination with the string in the string argument.
In Qlik Sense, all field values are potentially dual values. This means that the field values can have both a numeric value and a textual value. An example is a date that could have a numeric value of 40908 and the textual representation '2011-12-31'.
Information noteWhen 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.
Tip noteThe 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 filter panes.
Scripting examples
Example
Description
Add the following examples to your script and run it.
Load dual ( NameDay,NumDay ) as DayOfWeek inline
[ NameDay,NumDay
Monday,0
Tuesday,1
Wednesday,2
Thursday,3
Friday,4
Saturday,5
Sunday,6 ];
The field DayOfWeek can be used in a visualization as a dimension for example.In a table with the week days are automatically sorted into their correct number sequence, instead of alphabetical order.
Load
Dual('Q' & Ceil(Month(Now())/3), Ceil(Month(Now())/3)) as Quarter
AutoGenerate 1;
This example finds the current quarter. It is displayed as Q1 when the Now() function is run in the first three months of the year, Q2 for the second three months, and so on. However, when used in sorting, the field Quarter will behave as its numerical value: 1 to 4.
Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) as Quarter
As in the previous example, the field Quarter is created with the text values 'Q1' to 'Q4', and assigned the numeric values 1 to 4. In order to use this in the script the values for Date must be loaded.
Dual(WeekYear(Date) & '-W' & Week(Date), WeekStart(Date)) as YearWeek
This example create sa field YearWeek with text values of the form '2012-W22' and at the same time, assigns a numeric value corresponding to the date number of the first day of the week, for example: 41057. In order to use this in the script the values for Date must be loaded.
Field
A field contains values, loaded from a data source. At a basic level, a field corresponds to a column in a table. Fields are used to create dimensions and measures in visualizations.
A dimension is an entity used to categorize data in a chart. For example, the slices in a pie chart or the bars of a bar chart represent individual values in a dimension. Dimensions are often a single field with discrete values, but can also be calculated in an expression.
A dimension is a dataset in a data mart that forms part of the star schema. Dimension datasets hold the descriptive information for all related fields that are included in the fact table’s records. A few common examples of dimension datasets are Customer and Product. Since the data in a dimension dataset is often denormalized, dimension datasets have a large number of columns.
