topic Re: Purge string in QlikView

Purge string

Wed, 19 Aug 2015 09:59:53 GMT

Hi,

some of my account numbers comes with seven zeroes in fornt, need to get rid of them, example below:

000000088899

0000000777111

00000003334

000000066666

should be presented as:

88899

777111

3334

66666

what would be the easiest way to remove these zeroes in the script?

Re: Purge string

Peter_Cammaert — Wed, 19 Aug 2015 10:01:10 GMT

=Num(Field, '#0')

Re: Purge string

PrashantSangle — Wed, 19 Aug 2015 10:03:45 GMT

Hi,

use num#(FieldName)

Regards

Re: Purge string

Wed, 19 Aug 2015 10:17:36 GMT

haha, was a bit fast there. Actually it turned all of the "problem accounts" into null.

Any idea why?

Sorry for the hassle.

//O

Re: Purge string

qlikmsg4u — Wed, 19 Aug 2015 10:19:30 GMT

Load Num(value) as Value;

Load * Inline [

value

000000088899

0000000777111

00000003334

000000066666

];

Re: Purge string

sunny_talwar — Wed, 19 Aug 2015 10:19:56 GMT

May be this:

LOAD Num(Num#(Num), '#0') as Num;

Re: Purge string

sasiparupudi1 — Wed, 19 Aug 2015 10:24:03 GMT

try num(num#(val)) as value

Re: Purge string

Wed, 19 Aug 2015 10:25:44 GMT

same result im afraid, null values

Re: Purge string

qlikmsg4u — Wed, 19 Aug 2015 10:27:58 GMT

It is working fine for me

Load value,Num(value) as Value1;

Load * Inline [

value

000000088899

0000000777111

00000003334

000000066666

];

Re: Purge string

PrashantSangle — Wed, 19 Aug 2015 10:34:51 GMT

Hi,

Can you post your test app???

Regards

Re: Purge string

Wed, 19 Aug 2015 10:34:59 GMT

Hi KN,

not sure how to implement it into my script.

Can you pls give me a hint?

//O

Re: Purge string

jagan — Wed, 19 Aug 2015 10:36:01 GMT

Hi,

The below script works fine

Load value,Num(value) as Value1;

Load * Inline [

value

000000088899

0000000777111

00000003334

000000066666

];

Please check.

Regards,

Jagan.

Re: Purge string

Wed, 19 Aug 2015 10:37:20 GMT

You can use this formula:

Num(PurgeChar(Text(FieldName), '0'), '000')

Re: Purge string

richnorris — Wed, 19 Aug 2015 10:40:38 GMT

When you get leading zero's like that, it tends to be because Qlikview is interperating the field as a string. This tends to happen if there is leading or trailing whitespace, but also happens sometimes when one or more rows contain string data. So, my first port of call would be to do a trim(), so maybe try:

=trim(num(num#(val)))

If you're still having trouble, I'd check the rest of your data, and make sure you don't have any fields in there that are obviously text.

Re: Purge string

Kushal_Chawda — Wed, 19 Aug 2015 10:41:44 GMT

Try,

Purgechar(YourField,'0') as NewField

Re: Purge string

Wed, 19 Aug 2015 10:43:47 GMT

Please find the attached application, i solved with the help string functions

Regards

AR UMAMAHESH

Re: Purge string

Wed, 19 Aug 2015 10:45:57 GMT

Purgechar some times not good

suppose if the number have zero between the other numbers, purgechar will remove that one also

Re: Purge string

kavita25 — Wed, 19 Aug 2015 10:48:49 GMT

Hi,

try this

REPLACE

(LTRIM(REPLACE(Field_name, '0', ' ')), ' ', '0')

Regards,

Kavita

Re: Purge string

qlikviewwizard — Wed, 19 Aug 2015 10:53:35 GMT

Hi,

Please use this.

Data:

Load *,

Purgechar(FIELD_Zero,'0') as FIELD_NoZero;

LOAD * INLINE [

FIELD_Zero

000000088899

0000000777111

00000003334

000000066666

];

EXIT Script;

Re: Purge string

Wed, 19 Aug 2015 10:55:25 GMT

This is the logic i implemented

Temp:

LOAD * INLINE [

Phone

000000088899

0000000777111

00000003334

000000066666

];

Final:

LOAD LTrim(Replace(Phone,'0000000','')) as PhoneNumber

Resident Temp;