topic Re: Multiple keep_chars in Keepchar string function? in QlikView

Multiple keep_chars in Keepchar string function?

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Multiple keep_chars in Keepchar string function?

sunny_talwar — Thu, 16 Jun 2016 15:22:39 GMT

Try this:

Load

[WR #],

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

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

From

;

Re: Multiple keep_chars in Keepchar string function?

sunny_talwar — Thu, 16 Jun 2016 15:23:34 GMT

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

Re: Multiple keep_chars in Keepchar string function?

Thu, 16 Jun 2016 15:31:37 GMT

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?

Re: Multiple keep_chars in Keepchar string function?

sunny_talwar — Thu, 16 Jun 2016 15:39:45 GMT

How about this:

Load

[WR #],

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

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

From

;

Re: Multiple keep_chars in Keepchar string function?

tamilarasu — Thu, 16 Jun 2016 16:03:58 GMT

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;