topic Re: Re: prefix avoid zeros in dimension in QlikView

prefix avoid zeros in dimension

Srinivas — Mon, 10 Mar 2014 16:06:55 GMT

Dear all,

Before Avoid zeros in My dimension.

can you resole ASAP..

000000000000001613
000000000000001614
000000000000001615
000000000000001616
000000000000001617
000000000000001618
000000000000001619
000000000000001620
000000000000001621
000000000000001622
000000000000001623
000000000000001624
000000000000001625
000000000000001626
000000000000001627
000000000000001628
000000000000001629

Thanks

Reddy

Re: prefix avoid zeros in dimension

giakoum — Mon, 10 Mar 2014 16:12:28 GMT

if it is always 4 digits then right(YourField, 4) would do it.

Or num# function to make it appear as number, eliminating leading zero's

Re: prefix avoid zeros in dimension

Mon, 10 Mar 2014 16:14:36 GMT

Munna,

I would use the functions

rangemin() to get a minimum between two data (we are not sure it will always begin with 0)

mid() to extract part of a string

findoneof() to know where to find one letter in a string

Please try this (I did not test myself):

mid(MyField, rangemin(findoneof(MyField, '123456789'), 1)) as MyField

Fabrice

Re: prefix avoid zeros in dimension

jerem1234 — Mon, 10 Mar 2014 16:17:38 GMT

You could try:

num(FIELD) or

num(num#(Field))

Hope this helps!

Re: prefix avoid zeros in dimension

srchilukoori — Mon, 10 Mar 2014 16:19:05 GMT

Findoneof() is a good option.

Right(<Field Name>, Len(<Field Name>) - FindOneOf(<Field Name>,'123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-=_+[]{}\|;:,.<>/?')+1)

SC.

Re: prefix avoid zeros in dimension

SunilChauhan — Mon, 10 Mar 2014 16:45:41 GMT

SUPPOSE THIS DATA COMES UNDER FIELDAME COLOUMN. THEN USE FOLLOWS

MID(FIELDNAME,FINDONEOF(FIELDNAME,'123456789'),lEN(FIELDNAME)-1)

HOPE THIS HELPS

Re: prefix avoid zeros in dimension

bgerchikov — Mon, 10 Mar 2014 16:48:29 GMT

Here is the simple one:

num(num#(replace(NumberExpression,'00000','')),'0000')

for some reason Num# doesn't work directly with this data.

Re: prefix avoid zeros in dimension

Mon, 10 Mar 2014 16:57:06 GMT

Easiest I can think of:

Its its in number format:

num(Field,'0')

In String format:

num(num#(Field),'0')

Thanks

Re: prefix avoid zeros in dimension

Srinivas — Mon, 10 Mar 2014 18:22:26 GMT

Hi Fabrice,

It's not working can you do another solution it would be great help full....

Thanks& Advance

Munna

Re: prefix avoid zeros in dimension

Srinivas — Mon, 10 Mar 2014 18:29:19 GMT

My Dear Friend,

I tried but,It's not working can you do another solution it would be great help full....

Thanks& Advance

Munna

Re: prefix avoid zeros in dimension

Srinivas — Mon, 10 Mar 2014 18:29:36 GMT

My Dear Friend,

I tried but,It's not working can you do another solution it would be great help full....

Thanks& Advance

Munna

Re: prefix avoid zeros in dimension

Srinivas — Mon, 10 Mar 2014 18:29:51 GMT

My Dear Friend,

I tried but,It's not working can you do another solution it would be great help full....

Thanks& Advance

Munna

Re: prefix avoid zeros in dimension

Srinivas — Mon, 10 Mar 2014 18:33:13 GMT

Hi,

Here my fields not fixed always some time 4 digits and some times 5 digits.

Re: Re: prefix avoid zeros in dimension

Mon, 10 Mar 2014 18:40:20 GMT

Attached with answer. Can you check it out.

Thanks

Re: Re: prefix avoid zeros in dimension

jerem1234 — Mon, 10 Mar 2014 18:43:16 GMT

I would think one of the solutions here would work. I have attached an example of using num and num#(). If none of the solutions listed here work, I would suggest uploading your app or an example of the problem you are facing (might be more complex than we thought).

Hope this helps!

Re: prefix avoid zeros in dimension

MK_QSL — Mon, 10 Mar 2014 19:28:06 GMT

Try

NUM(Evaluate(YourFieldName),'#0') as YourFieldName

Re: prefix avoid zeros in dimension

veidlburkhard — Mon, 10 Mar 2014 19:35:56 GMT

Hi Munna,

I loaded your data with leading zeros into QV and found this solution:

LOAD RawData,

replace(ltrim(replace(RawData,'0',' ')),' ','0') as NumData

FROM

[http://community.qlik.com/thread/110099]

This expression in a first step replaces all zeros with spaces, then the ltrim function eleminates all leading zeros. At last zeros which have been replaced in the relevant number with spaces, too are again switched to zeros.

Hope this helps

Burkhard

Re: prefix avoid zeros in dimension

jerem1234 — Mon, 10 Mar 2014 19:51:50 GMT

Looks like I provided an example that probably exemplifies why it doesn't work

Seems like num and num# has problems when the number exceeds 14 digits. I believe some of the new solutions provided below should solve the issue.

Re: prefix avoid zeros in dimension

Srinivas — Tue, 11 Mar 2014 04:05:56 GMT

Hi Manish,

It's working only for numeric only but not string and numeric.

like..

200N17000

200P30IF0

200P30000

000000000000001613

000000000000001614

000000000000001616

000000000000001618

000000000000001619

000000000000001621

000000000000001623

000000000000001624

000000000000001626

1681J1400N

000000000000001705

000000000000001718

1872E01000

2260V02000

Advance

Munna