topic Extracting Text Between Parenthesis in QlikView

Extracting Text Between Parenthesis

Mon, 01 Aug 2011 01:34:51 GMT

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?

Re: Extracting Text Between Parenthesis

nagaiank — Mon, 01 Aug 2011 02:50:17 GMT

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;

Re: Extracting Text Between Parenthesis

Mon, 01 Aug 2011 02:50:46 GMT

Hi,

See if the attached sample can help you.

Cheers.

Re: Extracting Text Between Parenthesis

Mon, 01 Aug 2011 02:59:52 GMT

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