Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have a field as per below:
Name | Tel | Unwanted Character |
A | #65#2310 | #65# |
B | 0262923506 | 0 |
C | 60008@172.30.33.75:5060 | @172.30.33.75:5060 |
D | #55#0262484535 | #55#0 |
E | 262692615186 | 262 |
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
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....
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/
hi.,
Try like this.
Num#(Replace(Replace(Replace(Replace(Replace(Replace(Tel),'#' , ''),'@',''),'.',''),':',''),'(',''),')',''))
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)
)
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
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