Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple keep_chars in Keepchar string function?

I am working with this field called '[WR #]' that contains numbers such as 2134,2213R,2153P,21451,24567R and 21633P. In order to make the numbers and letters show separately I used this in my script:

Load

[WR #],

SubField([WR #], 'R',1) as [WR ID],

KeepChar([WR #], 'R') as [Rework]

From

;

By using this I can see a table like this:

WR ID    WR #     Rework

2134        2134            -

2213        2213R          R

21451      21451          -

24567    24567R          R

The only problem with this is that I do not know how to get the P's included as well. I have tried using commas and and's in order to add additional letters to both the subfield and keepchar, but then it doesn't show anything on my dashboard. Is there a way to use multiple keep_chars in the Keepchar string function?

5 Replies
sunny_talwar

Try this:

Load

[WR #],

SubField([WR #], 'R',1) as [WR ID],

KeepChar([WR #], 'RP') as [Rework]

From

;

sunny_talwar

You can add all the letters or numbers you want to keep within the single quotes

Not applicable
Author

That didn't add the P's in. I also just noticed that the values with P's are missing from my dashboard. Is a way to do two subfields if WR ID is joining on different tables?

sunny_talwar

How about this:

Load

[WR #],

Num#(PurgeChar([WR #], 'RP')) as [WR ID],

KeepChar([WR #], 'R') as [Rework]

From

;

tamilarasu
Champion
Champion

Another solution using If statement. Not elegant but thought to share

Load [WR #],
If(Wildmatch([WR #],'*P','*R'), Left([WR #], Len([WR #])-1),[WR #]) as [WR ID],
Pick(WildMatch([WR #],'*P','*R')+1,'','P','R') as [Rework]

  From Source;