Parsing text to numbers and dates

Quite often, we have to deal with data that are not in a format that QlikView can interpret as either dates or numbers. In these cases, we need to look at the data interpretation functions, Date# and Num#.

These functions accept a value and a format string. They use the format string to attempt to interpret the value.

In this recipe, we will look at using Date# and Num# functions to interpret our data correctly.

Getting ready

Load the following script:

Load
  Date#(Month, 'DD.MM.YYYY') As Month, 
  Num#(Sales, '#.###,##', '.') As Sales
Inline [
  Month, Sales
  01.01.2013, 1.000
  01.02.2013, 976
  01.03.2013, 1.100
];

How to do it…

These steps show you how to parse text to numbers and dates:

1. Add a listbox for Sales and Month onto the layout:

   

2. Note that the values are right-justified and that they retain the original formatting.

How it works…

The # functions apply a format string to the incoming value. If it parses correctly as number (or date) then it loads the value...