You’ve probably heard of the QV “Dual” function and may have seen some interesting uses for dual(). One of my workhorse applications of Dual is flag fields.
In script, we might create flag fields such as:
if(Not IsNull(ShipDate), 1, 0) as Shipped
This creates the new field “Shipped” that contains 1 (if the corresponding ShipDate has a value) or 0. This is a useful flag for chart expressions like “if(Shipped, …” or “sum(Shipped)” or “OrderTotal * Shipped “.
If we display the flag in a Listbox or Table, it looks ugly. 0’s and 1’s are appreciated only by programmers.
If instead of 0/1 we assign string values like “Yes”, “No” the display would look better.
But if we use string values, we lose the ability to use numeric expressions. Instead we would need to use slower string expressions like “if(Shipped=’Yes’,…)” and “count(Shipped).”.
Wouldn’t it be great if we could have the best of both worlds? Strings for display and numbers for convenience and performance? This is where dual is useful.
A quick review of the function from Help:
dual( s , x )
Forced association of an arbitrary string representation s with a given number representation x.
What this means is that a field value will have both string and numeric values — a dual nature. Which type is used — string or number — is dependent on the context where the field is referenced.
Let’s define that Shipped flag using dual:
if(Not IsNull(ShipDate), dual(‘Yes’, 1), dual(‘No’, 0) ) as Shipped
We are still assigning one of two choices to Shipped. The assigned values have a dual nature. Qlikview will use either the string or numeric value automatically based on how the field is referenced.
In a display context like a Listbox or dimension, the string value (“Yes/No”) will be used.
In a numeric expression like “sum(Shipped), the numeric value (0/1) will be used.
What about the “(If(…” test? QV will choose a numeric or string comparison based on the presence of quotes. All three of these examples are valid:
If(Shipped, …
If(Shipped=1, …
If(Shipped=’Yes’, …
In practice, I may create many 0/1 flags in a single script. Rather than clutter the syntax with a lot of repetitive dual(), I use a mapping approach.
First I define my map:
YesNoMap:
MAPPING LOAD num, dual(str, num) INLINE [
num, str
0, No
1, Yes
];
Then I identify the fields I want to assign to dual() with MAP USING statements:
MAP Shipped USING YesNoMap;
MAP Audited USING YesNoMap;
Later in the script I create the flag fields using the simple 0/1 assignments:
if(Not IsNull(ShipDate), 1, 0) as Shipped
MAP USING will translate the 0/1 into the desired dual. This approach also makes it easier to globally redefine my display values from “Yes/No” to “Y/N” or “Si/No”.
-Rob
Great tip using maps for decluttering the script. Will be using this from now on. Thanks!
Hi Rob
Nice post!
How does one go about referencing the 1 in set analysis. I cant seem to get it to work, eg
field = {1}
Thanks
Set analysis modifiers use the “search” syntax, therefore set analysis will always assume string. So you must use
field={yes}
To reference the 1 in SA you must use the more complex
field={“=field=1”}
-Rob
Really nice Rob, and very useful.
Thanks for your sharing your knowledgement.
Duals are a good idea but keep in mind that it will fail if you have indistinct numeric values:
Test:
LOAD dual(str, num) INLINE [
str, num
A, 1
B, 2
C, 2
D, 3
];
Value ‘C’ will never get shown (is totally lost) because number 2 has “occupied” value “B”.
Brilliant, Rob!
Thanks for sharing!
Hi Rob,
although this blog is a bit old ….
Is there a way to work with the result of a boolean comparion with DUAL, i.e.
use it for an expression like
(ShipDate < Today()) AS Shipped
?
I’m not understanding the question from your example. Do mean something like
Dual(ShipDate < Today())) ?
Dear Rob,
sorry for not replying, but still have this problem:
Actually I am looking for a way to apply a DUAL-function to a true boolean value:
(ShipDate < TODAY()) AS check
would deliver boolean values -1 and 0.
This field I would like to convert to a DUAL-value, so that e.g. in the GUI it would display as 'YES' or 'NO'.
You can certainly do it with an if():
if(ShipDate < TODAY(), dual('Y',1), dual('N',0) AS check
If you want to do it indirectly use a map:
YNMap:
Mapping
LOAD
Key, Dual(str, num)
Inline [
Key, str, num
0, No, 0
-1, Yes, 1
]
;
MAP check USING YNMAp;
...
Evaluate(ShipDate < TODAY()) AS check
It's a quirk that you have to use Evaluate().
Thanks!