Data Types in QlikView

[hic]

There are no data types in QlikView.

This is not a limitation – it is a conscious design decision.

One of the initial requirements of QlikView was that it should be possible to mix data from different sources: We wanted users to be able to have a data model with some data from a database, some from an Excel sheet, and some from a comma delimited text file. Some of these sources have proper data types, others don’t. So relying on the data types of the data source would be difficult. Further, we wanted the internal functions to be able to always return a relevant calculation – there must never be any type of conversion problems. We wanted simplicity for the user.

Enter the Dual format.

The dual format is a brilliantly simple solution to the above requirements: Its core is that every field value has two values – one string that is displayed, and one number that is used for sorting and calculations. The two values are inseparable; they are like the two sides of a single coin. They are both needed to describe the field value properly.

For example, months have the string values ‘Jan; ‘Feb’ … ‘Dec’, which are displayed. At the same time they have numeric values 1 to 12, which are used for sorting. Similarly, weekdays have the string values ‘Mon’; ‘Tue’ … ‘Sun’ and at the same time the numeric values 0 to 6.

Dates and times have string values that look like dates, e.g. ‘12/31/2011’ or ‘06.06.1944 06:30’ and at the same time they have numeric values corresponding to the number of days since Dec 30 1899. As I write this, the (numeric) time is 41215.6971. How months, weekdays, dates and times should be displayed is defined in the environment variables in the beginning of the script.

This way QlikView can sort months, days and dates numerically, or calculate the difference between two dates. Numbers can be formatted in arbitrary ways. In essence, QlikView can have data that is much more complex than plain strings.

When dual values are used as parameters inside QlikView functions, the function always uses just one of the two representations. If a string is expected, as in the first parameter of Left(s,n), the string representation is used. And if a number is expected, as in the Month(date) function, the number representation is used.

QlikView functions all return dual values, when possible. Even the color functions do, see table. However, string functions, e.g. Left(s,n), is the exception; because they don’t have a number that can be associated with the string, they leave the number part of the dual empty.

Finally, there is of course a function with which you can create your own dual values: Dual(s,n). With it you can create any combination of string and number. Use it if you want to assign a sort order to strings or if you want to assign text to numeric values.

Here is how one of the top contributors of QlikCommunity uses duals for Booleans: Rob Wunderlich’s Dual flags.

HIC

Further reading related to this topic:

On Boolean Fields and Functions

Automatic Number Interpretation

[hic]

Founder? No, but one of the very first employees.

I was the R&D manager (although we didn't call it that then) during the first 2 and a half years. I had "Product Manager" on my business card, and that meant both product mgmt and project mgmt.

After this time I moved more towards customers: I was basically the first Solution Consultant that QlikView had and I have since then implemented numerous QlikView solutions.

The number of lines of C++ code I've written can however be counted on your ten fingers. There are some formulae for which I am responsible, but no - I am not a C++ developer.

HIC

[#3]

Hi Henric

I just copied the below from the environment variable.

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

If my week starts from Sunday instead of Monday, and I want to show the same, and I change the sequence of the days names as defined in the environment variable

as

SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

, will it work globally in all formulas, etc. in QV app?

Henric Cronström

[hic]

No, all that will happen is that all Mondays will be called "Sun". Not what you want...

The ISO 8601 is hard coded in the product. Unfortunately. This means that any function you use will assume the rules set by this standard, e.g.

• Monday is the first day of the week
• Week 1 is the week that contains Jan 4.

If you want to define your Week number and day sequence differently, you need to do that with your own formulae in the script. See example at the bottom of this blog post: http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/23/ancient-gods

HIC

Or better yet:

Redefining the Week Start

Redefining the Week Numbers

[#5]

Hi,

Is the conclusion correct, that QlikView will remember the first loaded text format for dual fields and adapt the text part of the data loaded thereafter for this field in the first loaded text format?

Example:

//- Format: YYYY-MM

[YYYY-MM]:

LOAD

    date(date#(MonthYear, 'MM.YYYY'),'YYYY-MM') as [YYYY-MM.Year-Month];

LOAD * INLINE [

    MonthYear

    01.2013

    02.2013

];

Concatenate ([YYYY-MM])

LOAD

    date(date#(YearMonth, 'YYYY-MMM'),'YYYY-MMM') as [YYYY-MM.Year-Month];

LOAD * INLINE [

    YearMonth

    2013-Jan

    2013-Mrz

];

//- Format YYYY-MMM

[YYYY-MMM]:

LOAD

    date(date#(YearMonth, 'YYYY-MMM'),'YYYY-MMM') as [YYYY-MMM.Year-Month];

LOAD * INLINE [

    YearMonth

    2013-Jan

    2013-Mrz

];

Concatenate ([YYYY-MMM])

LOAD

    date(date#(MonthYear, 'MM.YYYY'),'YYYY-MM') as [YYYY-MMM.Year-Month];

LOAD * INLINE [

    MonthYear

    01.2013

    02.2013

];

Regards,

Thomas

[hic]

Yes.

Or rather, the first loaded instance of a specific field value. So if the first instance of Jan is '2013-01' and the first instance of Feb is '2013-Feb', then you will have these two formats in the same field.

HIC

[#7]

But isn't that exactly the case in the example above?

For table [YYYY-MM] '01.2013' and '02.2013' are loaded in the format 'YYYY-MM', then in the concatenated load '2013-Jan' and '2013-Mrz' are loaded in the format 'YYYY-MMM'. So the first instance of '2013-Mrz' would be loaded in format 'YYYY-MMM', but the value is nevertheless shown as '2013-03'. Why that if the first loaded instance of a specific field value is the crucial factor for the format?

[hic]

You're right. I forgot about the formatting function... If you use a formatting function, you will have the same format for all values in the field. But remove the Date()-function calls and my previous answer will be correct - you will get mixed formats.

HIC

[sujeetsingh]

Awesome representation Henric

[pljsoftware]

Hi Henric,

the link is broken, please could you update with new The Nature of Dual() Flags | Qlikview Cookbook

Thanks a lot

Luca Jonathan Panetta

[#11]

Awesome representation