Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

PurgeChar or Replace

Hello All,

I have a field as per below:

   

NameTelUnwanted Character
A#65#2310#65#
B02629235060
C60008@172.30.33.75:5060@172.30.33.75:5060
D#55#0262484535#55#0
E262692615186262
F(02)62722366(02)

I need to clean that 'Tel' field  and kind of standardise it.

Any idea on how I can do this? (Like exclude my unwanted  characters and make the number like 10 or 12 numbers)

Many Thanks,

Hasvine

6 Replies
sunny_talwar

Is Unwanted Character another field that is available to you? If it is, then may be this:

LOAD Name,

          Num#(Replace(Tel, [Unwanted Character], '')) as Tel

From....

Miguel_Angel_Baeyens

Sunny's answer will work in most cases above, and is actually a good idea, but you need more a regular expression syntax like in case 2, where I guess it is the leading 0 the one you want to remove. Replace will remove all occurencies of the 0 in that example.

See here How to use regular expressions and here http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/

Anonymous
Not applicable
Author

hi.,

Try like this.

Num#(Replace(Replace(Replace(Replace(Replace(Replace(Tel),'#' , ''),'@',''),'.',''),':',''),'(',''),')',''))

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

You can try

Mid(Tel,

  If(Index(Tel, [Unwanted Character])=1,Index(Tel, [Unwanted Character])+Len([Unwanted Character]), 1),

  If(Index(Tel, [Unwanted Character])=1, Len(Tel) - Index(Tel, [Unwanted Character]), Index(Tel, [Unwanted Character]) - 1)

)

Help users find answers! Don't forget to mark a solution that worked for you!
Not applicable
Author

Hi Sunny,

No, unfortunately the unwanted characters are not a field available to me.

As it is not specific, I do not know how to clean the data in the field.

Thanks and Kind Regards,

Hasvine

sunny_talwar

What is the logic for sometime removing things from the start and removing them from the back at times? May be look into Reg Exp as pointed out by mbaeyens