Data Load of varchar data that QV interprets as a number

[#1]

I have a situation where we are loading data from SQL from a field that is varchar. The field from the database stores data that looks like numbers (example 2.1, 2.2, 2.3....2.10,2.11). When we load this into Qlikview 2.1 and 2.10 are treated the same when in fact they are not the same data but they are grouped together. We have this field as Mixed but this does not help as it appears to have removed the trailing zero. Any thoughts on how to bring this data in and retain its true value?

[johnw]

If you just want it to be a text field:

text(YourField) as YourField

If you sometimes need 2.1 and 2.10 to be treated as numbers (and thus the same) and sometimes as text (and thus different), I believe you would do this:

dual(text(YourField),YourField) as YourField

With the dual() field, when you use the field like a number, such as multiplying it by 5, it should treat it as a number. If you use it like text, such as using it as a dimension in a chart with a non-continuous axis, it should treat 2.1 and 2.10 as distinct values.

[#3]

Hi John

I have the same issue, and I can't put text() in the SQL SELECT statement (ole db), because then the statement seems to be invalid (no table is loaded into memory). Using text() around the field afterwards is to late.

Any solutions?

[#4]

Nevermind, just found the solution to load data from SQL and to use the text function in between...

TableName:

Load

     *, text(yourfield) as yourfieldastext

;

SQL SELECT * FROM database.dbo.table;

Best regards,

Jan

[chiranthsh]

Any updates here? I still have same issue at our end in Qliksense

[MarcoWedel]

"When several data items read into one field have different string representations but the same valid number representation, they will all share the first string representation encountered."

(https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/Formatt...)

hope this helps

Marco