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

Extracting Text Between Parenthesis

G'day;

This question is quite similar to: http://community.qlik.com/message/135716#135716

In fact it is for the same application with a data set that is just... messy... Not much I can do about that though.

There is a particular field that holds the Customer ID along with some other junk information. There is one initial issue that I think will be easy enough to code, however there is a few 'spanners' to throw into the wreck...

Example Set:

(22883) PRODUCT DATA -1

(33782) MORE RANDOM STUFF 22-3

(637286) Example Line 77

21-1 OMPE 111

772891 KKiIM 88-2

Now, first and foremost I need to extract the numbers between the parenthesis in the first thre lines.

I need to exclude the line that had a number where LEN() < 4... (ie: Line 4)

I also need to include the number from line 5 that doesn't have a parenthesis around it.

Then need to save it all into a new field called CustomerID or similar.

So the example set should turn into:

22883

33782

637286

772891

How would I best do this?

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

See if the attached sample can help you.

Cheers.

View solution in original post

3 Replies
nagaiank
Specialist III
Specialist III

The following script does what you want and saves the CustomerID values. There may be other ways of doing this also.

Data:

LOAD *, PurgeChar(SubField(String,' ',1),'()') as Part1;

LOAD * Inline [

String

(22883) PRODUCT DATA -1

(33782) MORE RANDOM STUFF 22-3

(637286) Example Line 77

21-1 OMPE 111

772891 KKiIM 88-2

];

Customer:

LOAD Part1 as CustomerID Resident Data

where Len(Part1) > 4 and Len(PurgeChar(Part1,'0123456789'))=0;

Not applicable
Author

Hi,

See if the attached sample can help you.

Cheers.

Not applicable
Author

Rock on! Thanks guys. BlackRockS that was really good, works a treat.