Qlikview changes numbers stored as text into numbers when loading into a QVD

[#1]

Hi,

I am trying to load from an SQL table into a QVD file and notice that Qlikview sometimes changes values that are stored in varchar fields into numbers. For instance, if the value that is stored in the SQL table is 014708, I noticed that the value in the QVD shows up as 14708. The trouble is that this does not happen consistently since I notice that other values are retained as strings in the QVD although they are picked from the same table and column. Is there any way of forcing Qlikview to consider all of them as strings and not resort to this arbitrary conversion?

I am using Qlikview 9

[#2 · Accepted solution]

Use text(Field)

[johnw]

By default, fields are considered to be mixed format. When it finds numbers, it treats those values as numbers. When it finds text, it treats those values as text. This can get particularly funny if your text value has an 'E' in it, and QlikView interprets it as scientific notation. In any case, it's easy to fix. When loading, do it like this:

text(FieldName) as FieldName

That will force QlikView to always consider it to be text data, even when there's a number in the field.

[pover]

The surest way to do it is to explicitly use the text() function for the columns in question.

Regards.

[Anonymous]

I love how someone posts a question at 8:55am and there's three excellent answers 6 minutes later!

[Anonymous]

Hi All,

It's strange but true. QlikView behaving strange, even forcing text(Field) for less volume data it takes the text (string) while exporting as well. but when I am exporting large volume of data it treats even text as number so that any number prefix with zero is getting removed. Example : 0000111 will come as 111 which I dont want. Need help.

Best Regards,

Shubham

[#7]

Text() didn't work for me on version 10