Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.png "Former Employee"){kind=icon}
I have in several previous blog posts written about the importance to interpret dates and numbers correctly e.g. in Why don’t my dates work?. These posts have emphasized the use of interpretation functions in the script, e.g. Date#().
But most of the time, you don’t need any interpretation functions, since there is an automatic interpretation that kicks in before that.
So, how does that work?
In most cases when QlikView encounters a string, it tries to interpret the string as a number. It happens in the script when field values are loaded; it happens when strings are used in where-clauses, or in formulae in GUI objects, or as function parameters. This is a good thing – QlikView would otherwise not be able to interpret dates or decimal numbers in these situations.
QlikView needs an interpretation algorithm since it can mix data from different sources, some typed, some not. For example, when you load a date from a text file, it is always a string: there are no data types in text files – it is all text. But when you want to link this field to date from a database, which usually is a typed field, you would run into problems unless you have a good interpretation algorithm.
For loaded fields, QlikView uses the automatic interpretation when appropriate (See table: In a text file, all fields are text - also the ones with dates and timestamps.) QlikView does not use any automatic interpretation for QVD or QVX files, since the interpretation already is done. It was done when these files were created.
The logic for the interpretation is straightforward: QlikView compares the encountered string with the information defined in the environment variables for numbers and dates in the beginning of the script. In addition, QlikView will also test for a number with decimal point and for a date with the ISO date format.
If a match is found, the field value is stored in a dual format (see Data Types in QlikView) using the string as format. If no match is found, the field value is stored as text.
An example: A where-clause in the script:
Where Date > '2013-01-01' will make a correct comparison
The field Date is a dual that is compared to a string. QlikView automatically interprets the string on the right hand side and makes a correct numeric date comparison. QlikView does not (at this stage) interpret the content of the field on the left hand side of the comparison. The interpretation should already have been done.
A second example: The IsNum() function
IsNum('2013-01-01') will evaluate as True
IsNum('2013-01-32') will evaluate as False since the 32:nd doesn't exist
In both cases, strings are used as parameters. The first will be considered a number, since it can be interpreted as a date, but the second will not.
A third example: String concatenation
Month(Year & '-' & Month & '-' & Day) will recognize correct dates and return the dual month value.
Here the fields Year, Month and Day are concatenated with delimiters to form a valid date format. Since the Month() function expects a number (a date), the automatic number interpretation kicks in before the Month() function is evaluated, and the date is recognized.
A final example: The Dual() function
Dual('Googol - A large number', '1E100') will evaluate to a very large number
Here the second parameter of Dual() is a string, but QlikView expects a number. Hence: automatic interpretation. Here, you can see that scientific notation is automatically interpreted. This sometimes causes problems, since strings – that really are strings – in some cases get interpreted as numbers. In such cases you need to wrap the field in a text function.
With this, I hope that the QlikView number handling is a little clearer.
Further reading related to this topic: