Monday 19 March 2012

When QlikView Data Types Go Wrong

Writing the previous post Removing Leading Zeros reminded me of a lesson I learnt (the hard way) a few years ago about how QlikView perceives data types.

Anyone who has worked with QlikView for more than a few days will have realised that you don't have to explicitly tell it which data type a field contains. Instead, QV decides for itself and it does this at a value level rather than having a single data type for every entry in a field. Normally this makes a developer's life very simple and helps to speed up the process of building data models. I'm sure no one, including myself, would argue it is a bad thing but there is one situation where this can cause you a huge headache.

QlikView is possibly a little too clever when it comes to what it considers to be a number, and as well as looking for values which are composed of all digits, it looks for other possible number formats also. For example, QlikView considers that a value of 5E3 means 5 times 10 to the power of 3 which if you perform the math is 5000 in digit form.

On the surface this might not seem a problem but a few years ago I came across a customer who was complaining of lost products in their QlikView application and after scratching my head for 2 days trying to find the problem I finally discovered the cause of the issue. The client's product IDs where made up of 6 hexadecimal characters. For those who aren't familiar with hexadecimal, as well as the numbers 0 to 9, hex uses the letters A through F also and this obviously means its perfectly possible to have values such as 0005E3. And as you will have guessed by now, this was considered the same product as 005000.

What makes this problem even more difficult to find is that QlikView displays whichever of two values it happens to come across first. So if it finds product 0005E3 first, product 005000 seems to have disappeared and its data is linked to the former. If it comes across product 005000 first, product 0005E3 seems to have disappeared instead.

Thankfully fixing the problem is a lot easier than finding it and simply wrapping the field in the text() function forces QlikView to consider them as strings rather than numbers and thus won't consider the values as the same.

text(ProductID) AS ProductID

The chances of you stumbling upon this same issue are pretty slim but if you ever do, knowing this will hopefully save you a lot of time and a very real headache. That aside, hopefully I've made you think a little about how QlikView interprets the data you give it.

2 comments:

  1. Hey, I've stumpled on often. I'd prefer to use the database server to define this correctly. Qlikview honers a strongly defined database.

    A similar thing happens if you have a numeric key that is more than 16 chars in length, then it needs to be defined as text. Else it end up as 1.23456789012345*10^2 which messes up the key.

    /Seebach

    ReplyDelete
  2. Hi! I have a similar problem, I did that, and working. But, Nowdays I need use Direct Select to access more info from my DB, and I can't format using Direct Select. Do u know something about that?

    ReplyDelete