QlikView Data Types – Dual Data Format & Interpretation, Representation

In our last tutorial, we discussed the QlikView Quick Chart Wizard. Here, we come with a new topic, QlikView Data Types. Moreover, we will learn dual data format, data interpretation, and data representation of Data Types in QlikView.

QlikView has a unique story for its data types and how they work. Pay special attention if you interested in the tool from a developer’s point of view. Because a good understanding of data types will help you in the long run.

So, let’s start QlikView Data Types Tutorial.

QlikView Data Types – Dual Data Format & Interpretation, Representation

What is QlikView Data Types?

Unlike any other programming language or a software relying on it, QlikView does not follow a standard defined data types system. The most commonly used data types in programming languages generally are int, float, double, string, array etc. Such data types can use by several RDBMS as well.

A question that now must be buzzing in your head is why there are no data types in QlikView? And how does it store and interpret data values load in its memory? Well, be patient as we unfold this mystery. To start with, the reason for no data type is that QlikView needs the flexibility to be able to load data from different sources using data values having varied data types.

As we know, QlikView loads data from various data sources like Excel files, databases like SAP, Oracle, then from CSV files, XML files, QVD files etc. All of these different data sources use different sets of defined data types or some do not use any at all.

In such a scenario, if QlikView also sticks to a defined or standardized data type and evaluates data values formatted only in that data type, then the situation will go a little out of hand. Many conversions of data types between source and QlikView has to perform which in turn will demand efforts from QlikView’s end like more resources, more time for converting data types and a much slower processing speed. This will also make the system prone to errors.

Do you know what is QlikView Delimited File (CSV)?

The Solution for No Data Types in QlikView

So, instead of relying on the data types of the source and making things complex, QlikView opted for not using any traditional data types for the data values loaded in its memory.

Having no data type initially turned some professionals and developers against this idea and made them sceptical about data safety. But however, this did not last for long as the rationale and benefits of this system far outweighed the objections.

The ‘No data type’ concept lets the associations between data fields and feasible because regardless of the data types used at the data source, all the values take a uniform code of data identity and easily associate with a similar data value.

Instead of following traditionally used data types, QlikView uses certain methods of data interpretation and representation. The data values in QlikView can represent in two ways (text or number) and stores in two parts (of a single unit); String part and a Number part.

QlikView Data Types – Modes of Representation

Data loaded from any type of data source, will stores internally into QlikView in two forms i.e. String and Number. It can also said that string and number are the two modes of representation of data stored in QlikView.

• The string type, widely used as dimension values of fields in a table, or values in a list box and other sheet objects.
• The number representation, use when numeric values uses which can also use in sorting and calculations.

During loading of data in QlikView from source, how a data value must represent and decide by QlikView itself.

QlikView Data Types – Dual Data Format

The issues regarding QlikView and data types in section 2 of this tutorial lead the developers to come to an ingenious solution. To counter the issues, the concept of Dual data format was introduced in QlikView. At the core of this concept is the fact that every data value being stored in QlikView stores in two forms or two parts i.e. a string part and a corresponding number part.

Any field value will display using the string part and sorting and calculation actions will do using the number part. These two forms of data of a value cannot use separately, they occur in a pair. For instance, if a date is being loaded into QlikView, then that date will have a pair of string-number data formats.

Follow this link to explore QlikView Operators

Following the dual data format, months store in two ways, a string like Jan, Feb, Mar and so on. And, a corresponding number value to each month’s string value (refer table). Similarly, each weekday will have a string value (Mon, Tue, Wed, Thu etc.) and a corresponding number value (from 0 to 6). However, colors functions not store in two data formats because they only consist of a string type and the number part is left empty.

Dates also store in dual format. The date can be written in any format, it will be read by QlikView because the dual data format values are present. Similarly, time also register. An important thing to note here is that date and time’s string representation will also have numbers in it, like, 21/09/2018 and 12:30. But, this does not mean that it is their number representation.

The digits used to show date and time are a part of their string representation. The number part is a sequence of unique digits and represents individually giving the date and time identity during calculations and sorting.

The months, weeks, days, dates and time format for representation defined in QlikView script editor by default. Any calculations on the date and time used the number part of the dual format and perform the calculation on it, giving the final result and displaying it again as a string.

Functions also use the dual format in QlikView function’s parameter. The function only uses one of the two format types to evaluate in the function. It decides which data format, from string and number, should take. However, mostly all the functions return the result in dual data format.

You can also define and create a new data value with a pair of dual values (string and number) defined by you. The syntax of the function is,

Dual(s,n)

Where, the ‘s’ is the parameter where you define a string or display value for the field and ‘n’ is the number or numeric value for the field.

Qlikview Data Types – Data Interpretation

The data loaded into QlikView can interpret in two different ways;

Qlikview Data Types – Data Interpretation

i. Numeric Interpretation

If a data value like a number, date, time, currency, loaded into QlikView’s memory, they interpreted in a certain way depending on whether the value’s data type defined or not. A correct interpretation of the data gets loaded, necessary because QlikView has no data type of it’s own.

Let’s Learn QlikView Server Load Sharing (Clustering)

a. Defined

The data values set to have a defined data type or format when they already have a data format when they upload. QlikView just reads the pre-defined formatted data value and displays it as it is. Like a string of date or time or currency value represents as per the formatting is done at the source before loading into QlikView.

With number values, the original source format store in QlikView’s memory and can restore in case any change in the format has been made by the user.

The default defined formats for data values are:

• Integer (default format for number)

• Floating point numbers (for number)

• Currency (for currency)

• Time (for time, by ISO standard formatting)

• Date (for date, by ISO standard formatting)

• Timestamp (for date and time, by ISO standard formatting)

b. Not Defined

Data interpretation of such values whose format did not define at the source interpreted by QlikView by analysing six factors.

• Way in which data is written at the source.

• Format settings of the operating system for date, time and currency.

• Optional number-interpreting variables.

• Optional formatting functions and interpretation functions.

• Document’s number formatting controls.

A reliable way to confirm whether the data values interpreted correctly or not is by checking for string values on left and number values on right of a list box. However, for values in the same field, interpretations may vary.

Must Refer – QlikView Skewness (Skew)

ii. Date and Time Interpretation

The date, time and timestamp values in a data set interpreted slightly differently. They store as a date serial number which refers to later, during a date and time calculations comparing two intervals etc.

• The serial number used to as an identity for the date and time values and is the count of a number of days passed since 30^th December 1899. The period in the range is 1^st March 1900 to 28^th February 2100. For example, serial number 35648 represents 1997-08-06.

• The serial number used for Time values is any value/number (including decimal point values) between 0 and 1. For example, the serial number 0.99999 is the value for time 23:59:59.

• The serial numbers for timestamps (date + time) is a value having mixed numbers. For example, the value 2.5 corresponds to January 1, 1990 at 12:00 noon.

Let us understand this with the help of an example. In below table, we see how several serial numbers interpreted by QlikView according to different formats.

QlikView Data Types – Date and Time Interpretation

So, this was all about QlikView Data Types Tutorial. Hope you like our explanation.

Conclusion

In this tutorial, we learned about a unique fact about QlikView data types i.e. Dual data Format. We saw how in QlikView, every data value has two types of data formats; string and number. In addition, we learned about modes of data interpretation and about dual data format in detail. Still had a query/doubt feel free to write us!

Related Topic – QlikView Backup and Restore

Reference