Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

 

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.

 

Automatic Interpretation.png

 

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.

HIC

 

Further reading related to this topic:

Data Types in QlikView

Get the Dates Right

Why don’t my dates work?

7 Comments
Employee
Employee

Thank you Henric

0 Likes
1,079 Views
Partner
Partner

Thanks , very helpful Henric

0 Likes
1,079 Views
emptyfish
New Contributor III

Its always good to understand fully how internals work, very helpful as usual Henric.

0 Likes
1,079 Views
Marcio_Campestrini
Valued Contributor

Thanks for the sharing, HIC

0 Likes
1,079 Views
Partner
Partner

Hi HIC,

Thanks for this post, even though you've written it long time back but it's still relevant.

What I released is when using Date in WHERE clause, interpreting the date as NUM seems to always work.

What's your thought on this?

Best Regards,

Gabriel

0 Likes
1,079 Views
MVP
MVP

Henric,

as I understood this feature, Qlik should try to interpret an argument to a function as a number, whenever it expects a number:

For example,

=Makedate('2 016', '6','17.00')

works fine, depending on your separator definitions.

But the functions fails when I use fields that show text values as arguments (even using simple integer style text values, with no fancy formatting).

So we need to use something like

=Makedate(Num#(Year), Num#(Month),Num#(Day) )

Is there any reason for that?

0 Likes
1,079 Views
robin_hausdoerfer
Valued Contributor III

is it possible to disable that feature globally?

----------------------------------------------------------------------

by the way: the automatic number recognition works also for text containing:

- time

- timestamp

- money

But only if t matches you configurated formats:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

(QV 11.2 SR15)

0 Likes
1,079 Views