Understanding Duals

QV stores all numeric data (numbers, dates, time, intervals, etc) in what is called “dual” format. Understanding dual format may help in overcoming some model pitfalls and may assist in solving some problems.

In brief, a dual format value comprises and numeric value, and a text representation. These may be standard number or date formats, they may be built in series (eg months, days of the week) or any customised series (more on this in a later post). The text representation is the equivalent of a format, but it can be more than that.

To illustrate, the number 40658 could represent:

• The value 40658. Depending on the format this could be displayed as 40,658 or 40658.00 etc.
• The date 18 April 2011.
• A product (or customer or branch or region etc) code 0040658
• etc

The number 0.65 could also represent:

• A percentage - 65%
• The time of day 3:36 pm (0.65 of a day of 24 hours from midnight to midnight)
• An interval of 15 hours 36 minutes (0.65 of 24 hours)
• Etc

The format can be created implicitly by QV. During a load, it will infer the format from the source data. If a certain column in a CSV data source contains numbers:

8.35000
9.15000
3.62333

Then QV will infer a format of 0.00000 for that field. Certain QV date and time functions also create the output in the default time and date format for your model.

Taking control – formatting commands

By default, QV will display the text portion of the dual on text boxes, captions, dimension/expression labels and the like, and will use the numeric portion for arithmetic expressions.

You can also manually control this with the Num() and Text() functions. For example, if a field “TimeElapsed” represents an interval with a value of 0.25 (6 hours), then Num(TimeElapsed) will return 0.25 and Text(TimeElapsed) will return 06:00:00 (assuming your default interval format is hh:mm:ss).

Some functions (like Min, Max, Avg) only return the numeric part of the expression, others return a dual value - check the return type in the auto-completion prompts or the documentation.

The formatting commands (Num(), Money(), Date(), Time(), TimeStamp(), and Interval()) allow you to control the text representation. It is worth pointing out that they have no effect on the numeric portion.

For example, Date(40651.65, ‘YYYY/MM/DD’) will display 2011/04/18, but the numeric value is still 40651.65. In other words, the Date function does NOT truncate the fractional part of the value, it simply does not display it. (To remove the time from date/time values, use Floor()).

That provides a very simple conceptual overview of dual values and formatting in Qlikview and I hope it helps with your understanding of dual values.

I will address some of “dual” issues in a little more depth in further posts on this site in the near future. I value your feedback and any topic suggestions or questions.