Discussion Board for collaboration related to QlikView App Development.
The data looks like below one of my reporting field
Database the field has data type character and values are not maintained the same length .
Data extraction it is extracted as
text(field1) as field1
I would like to remove leading 0 atleast for the values like 0000000000000007851 to 7851.
I tried Num,num#,trim ,left- nothing is working.
Advance thanks for your help.
seems to me like a bug. Have experimented and apparently QV interprets numbers with more than
14 leading "0"s always as a text, with lesser "0"s it would interpret a number like 0000000000000078517 as 78517.
You might use the Replace-function:
Replace(YourField, '00000000000000', '') AS TextOrNumber
but this will fail for e.g. 00000000000000078517A as it would display then 78517A and no longer the full string, which is probably needed.
This is why I recommend using replace with trim combination. Otherwise you will have problem with "long" numbers.
replace(ltrim(replace(OldMaterial, '0', ' ')), ' ', 0) as NewMaterial
our messages crossed - pretty nice solution. Though depending on the meaning of the data it might be of importance whether you have 0000456A or 456A ......