topic Re: How to remove characters in between a string. in QlikView

How to remove characters in between a string.

Jesh19 — Fri, 03 Mar 2017 05:06:53 GMT

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

Re: How to remove characters in between a string.

ajsjoshua — Fri, 03 Mar 2017 05:44:37 GMT

Hi,

Try this

trim(subfield(subfield(9989504213 - A2B , '-', 2),'(', 2))

Regards,

Joshua.

Re: How to remove characters in between a string.

rahulpawarb — Fri, 03 Mar 2017 05:47:35 GMT

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

Re: How to remove characters in between a string.

Anil_Babu_Samineni — Fri, 03 Mar 2017 06:16:55 GMT

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)

];

Re: How to remove characters in between a string.

avinashelite — Fri, 03 Mar 2017 06:34:32 GMT

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

Re: How to remove characters in between a string.

Jesh19 — Fri, 03 Mar 2017 06:46:02 GMT

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