Hello, hope someone can help with little issue I've come across.
We are pulling data into Qlikview from a SQL DB via ODBC connection etc. I've noticed issue with one particlar field I am importing in how qlikview is handling the data within the field.
the field in question is "Equipment Code" and is use to tag assets wihin our building which we plan maintenance on. I found 2 equipment numbers which are as follows: "002281" and "0002281". if you ignore the zero's you could argue they are technically the same number, but in the database they are two records. The equioment code field is a char data type not number/interger etc so it allows these as 2 unique entities!
Problem is when I load the script in Qlikview and look at these in a list box for example they are both shown as "0002281". I need them to be shown as their original unique values, as they appear in the source database. Is there a format/data type option etc that I can enforce on the field before running & reloading the script in qlikview to ensure the values are brought in as "002281" and "0002281"?
And before anyone suggests it No I can't alter the equipment codes, remove the leading zeros and make the values unique that way (don't know why they been entered like this, they just are. And we have historical work orders against these so they have to stay as is!!!)
In the Preceding Load, use the text() function to load the values without numerical representation.
The QV symbol table works here against you. QlikView will store dimension values in the field symbol table as a dual value, compoosed of a numerical value and a string representation. For the two dimension values you mentioned, the numerical value is the same. the first value is stored alliright. After a symbol table lookup for the second value QlikView will discover that the value already exists (with a different string representation though, but that's not important). The stored one will prevail, and the second one will be thrown away.
For every numerical value, QlikView keeps only one string representation. The one you are getting is probably the one that is loaded first.
In addition to what Henric posted, QV formats the numbers in that field according to the first record. So 0002281 is the first record and Qliview says, 'aha, a number, formatted like 0000000' and applies that rule to the next one too, so they both appear as '0002281'. Underneath they are both the number 2281, unless they are forced to text, as Henric explained.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
followed your canonical date blog too, was really useful in helping us as we have 3-4 date fields in one table and needed means to view all values for given month, using one selection etc - thanks for that too !!!