topic Re: Leading zeros in QlikView

Leading zeros

Wed, 26 Sep 2012 08:33:03 GMT

Hi guys.

I need help on an issue. I have a view in sql server that brings two records 014801 and 14801.

These are two different items.When i make a select from that view on a qvw the result is that it brings only one of the records.

I have tried convert(varchar,item) but it didnt work. Can anyone help?

Re: Leading zeros

rajni_batra — Wed, 26 Sep 2012 08:35:27 GMT

try to convert it into text by using text(records) then it would treat both values differently

Re: Leading zeros

Wed, 26 Sep 2012 08:51:27 GMT

replace(ltrim(replace([FIELD], '0', ' ')), ' ', 0) as FIELD

Re: Leading zeros

sudeep_d — Wed, 26 Sep 2012 08:51:44 GMT

hi,

if 014801 is in field name 'item',then

replace(ltrim(replace(item,'0',' ')),' ','0') as item

sudeep

Re: Leading zeros

Wed, 26 Sep 2012 09:00:48 GMT

What i do is that I store the result of the select into a qvd and then I load the qvd using the text(item) but it doesnt work correctly. It brings only the 014801.

Re: Leading zeros

Wed, 26 Sep 2012 09:03:28 GMT

Do not use the text function - use the following formula to get rid of leading zeros

replace(ltrim(replace([FIELD], '0', ' ')), ' ', 0) as FIELD

Re: Leading zeros

Wed, 26 Sep 2012 09:11:32 GMT

The problem is that I want to show both values 014801 and 14801.

If I use the replace function , it will give me only the 14801.

Re: Leading zeros

Wed, 26 Sep 2012 09:13:36 GMT

Im not too sure why you would want to use both fields

But you can use the replace function and the other way and have them named differently

Re: Leading zeros

Wed, 26 Sep 2012 09:17:55 GMT

014801 and 14801 are two different records. I want to show them both along with quantities. What happens is that the sql result is 014801 with quantity 100 and 14801 with quantity 200. QV shows 014801 with quantity 300.

Re: Leading zeros

sudeep_d — Wed, 26 Sep 2012 09:20:17 GMT

hi,

if u want to bring 1 as number and other as text then,

if(item=14801,'14801',item)

result:

14801

014801

sudeep

Re: Leading zeros

Wed, 26 Sep 2012 09:30:07 GMT

The thing is that I have hundreds of this case, 014802 and 14802,014803 and 14803 and so on...

Re: Leading zeros

sivarajs — Wed, 26 Sep 2012 09:51:03 GMT

This might be you are looking for

Re: Leading zeros

Wed, 26 Sep 2012 09:53:57 GMT

Thank you!

I added a load statement before the select and it worked!

Thank you all guys!