Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am having a requirement where i need to cleanse my data.
I know the usage of purgechar and i have tried it, but of no use.
Could you please suggest any other possible alternative for the below mentioned requirement.
My requirement is something like this:
I am having a department field with values
Department Expected Outcome
9989504213 - A2B A2B
Other (123456789) Other
Please do the needful.
Thanks in advance.
Regards,
Jeshwanth
Hello Eveyone,
Thanks for your help.
I got the solution.
I have tried something like this and it worked out very well.
Load * Where Not( IsNum(Department)) and Ord(Department) <> 0;
Load
SubField(PurgeChar(SubField(Department,'-'),'()'),' ') as Department.
Regards,
Jeshwanth B
Hi,
Try this
trim(subfield(subfield(9989504213 - A2B , '-', 2),'(', 2))
Regards,
Joshua.
Hello Jeshwanth,
Trust that you are doing well!
I have few queries;
1. Field Department consists of two parts. In given examples, '-' and ' ' are used as separators. What all other separators available under Department field?
2. You are willing to remove numbers - plain or surrounded by round brackets. What is the position of such numbers (First part of string or Second part)?
3. Numbers part of string will be kept intact. Is it correct?
Answers to these questions will help us to provide you expected feedback.
Regards!
Rahul
I am not sure, Can you send real data i mean atleast 10 values from Department
If less than expected values you can go with below
LOAD Department,If(Index(Department,'A2B'),'A2B',If(Index(Department,'Other'),'Other')) as Department1 Inline [
Department
9989504213 - A2B
Other (123456789)
];
We need a pattern or position to Identify the words, other wise it would difficult to handle this ...Ask you data team have it in proper format so that we could split the values
Hello Eveyone,
Thanks for your help.
I got the solution.
I have tried something like this and it worked out very well.
Load * Where Not( IsNum(Department)) and Ord(Department) <> 0;
Load
SubField(PurgeChar(SubField(Department,'-'),'()'),' ') as Department.
Regards,
Jeshwanth B