replace(ltrim(replace(MyField,'0',' ')),' ','0') AS MyField
This code first uses the replace() function to replace all zeros in the field with a space and then uses the ltrim() function which removes spacing from the begining of a string only. We then use replace() again to convert any remaining spaces back to zeros.
If it is only digits in "MyField" you should be able to use num(MyField) alt. Num(MyField, 'YourFormat') to remove the leading zero.
ReplyDeleteHi Vegar
DeleteIn that instance, QlikView actually stores the leading zeros still and simply formats the number to effectively hide them. This normally doesn't cause an issue for numbers though as QlikView would equate 00012345 to be the same value as 12345 anyway.
Matt
I really like this solution. The only potential flaw is if the data contains spaces in any value. Like '000123 333'. We want '123 333' as the result, instead we will get '1230333'.
ReplyDeleteHi Richard
DeleteYou are correct, although there is a solution to that problem assuming you can identify a character (or sequence of characters) that is impossible to occur in the strings. For example, if star (*) wasn't possible you could use the following:
replace(replace(ltrim(replace(replace(MyField, ' ', '*'),'0',' ')),' ','0'), '*', ' ') AS MyField
Regards
Matt
Hi Matthew, this looks a very elegant solution! Would this work where values have a leading '<' sign instead, for example recording '<5' as 5?
ReplyDeleteHi James, The short answer is yes although you really need to consider other possible values in the field. Can '<' appear anywhere else in the strings? If so do you wish to replace all of them or only when they appear at the start? Can only one '<' be at the start or is '<<' or '<<<' possible? As Richard pointed out above, are spaces possible in the field?
Delete