A data value is stored as numeric representation in a data source, for example 500, is padded with zeros (for example 0500) when loaded into QlikView.
The issue does not happen when loading only the affected value to QlikView using a WHERE clause:Environment:
Qlik Engine processes the LOAD / SELECT statement line by line. For data elements that have the same numeric value but different format (with and without padded zeros), Qlik Engine uses the format that comes first.
- Clean the data source and make sure there is no number with padded zeros for the filed under question
- Use function IsNum() to check if the value has a number format, and function Num() to convert the value to a number if it is. Note that this will convert both values 500 and 0500 in the data source to 500 in QlikView. For example:
If(isnum(TEXT),Num(TEXT),TEXT) as TEXT2;
SELECT * FROM "USER2"."CHARACTER_TEST";