tag:blogger.com,1999:blog-5585680429157536914.post2320550698045319673..comments2023-12-19T01:06:46.450+00:00Comments on QlikView Addict: Removing Leading ZerosMatt Fryerhttp://www.blogger.com/profile/16375702764551893303noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-5585680429157536914.post-73410081492699868782016-11-01T16:54:19.001+00:002016-11-01T16:54:19.001+00:00Hi James, The short answer is yes although you rea...Hi 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?Matt Fryerhttps://www.blogger.com/profile/16375702764551893303noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-82175684698293494072016-11-01T16:09:24.352+00:002016-11-01T16:09:24.352+00:00Hi Matthew, this looks a very elegant solution! Wo...Hi Matthew, this looks a very elegant solution! Would this work where values have a leading '<' sign instead, for example recording '<5' as 5?Anonymoushttps://www.blogger.com/profile/15090264407308292234noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-24219144326601103802016-08-08T18:39:21.658+01:002016-08-08T18:39:21.658+01:00Hi Richard
You are correct, although there is a so...Hi Richard<br />You 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:<br /><br />replace(replace(ltrim(replace(replace(MyField, ' ', '*'),'0',' ')),' ','0'), '*', ' ') AS MyField <br /><br />Regards<br />MattMatt Fryerhttps://www.blogger.com/profile/16375702764551893303noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-29451570407232477482016-08-08T18:30:03.425+01:002016-08-08T18:30:03.425+01:00I really like this solution. The only potential f...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'. Anonymoushttps://www.blogger.com/profile/13460363309199866414noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-2466328854028184222012-03-16T09:04:50.177+00:002012-03-16T09:04:50.177+00:00Hi Vegar
In that instance, QlikView actually stor...Hi Vegar<br /><br />In 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.<br /><br /><br />MattMatt Fryerhttps://www.blogger.com/profile/16375702764551893303noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-52332237112933789142012-03-15T19:15:34.079+00:002012-03-15T19:15:34.079+00:00If it is only digits in "MyField" you sh...If it is only digits in "MyField" you should be able to use num(MyField) alt. Num(MyField, 'YourFormat') to remove the leading zero.Vegarhttps://www.blogger.com/profile/01141935568676850768noreply@blogger.com