Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
Hi,
See if the attached sample can help you.
Cheers.
Rock on! Thanks guys. BlackRockS that was really good, works a treat.