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: 
cbaqir
Specialist II
Specialist II

Parse final characters if number

I need to parse out the a field so that IF there is a number, it is a separate field than the string text. 

The field is called DOC_VALUE_CLEAN:

 DOC_VALUE,
trim( SubField( trim(DOC_VALUE),',')) as DOC_VALUE_CLEAN,

In the examples below, those with a final numbers should have the string in a subfield called DOC_VALUE_PARSE and the number should be in a subfield called DOC_VALUE_CNT. As you can see, not all options have a final number.

2019-08-06_7-56-23.jpg

Labels (3)
1 Solution

Accepted Solutions
Marcos_rv
Creator II
Creator II

try this:

if ( wildmatch(right(DOC_VALUE, 1 ) , '1','2','3','4','5','6','7','8','9','0') > 0 , SubField(DOC_VALUE, ' ' , -1 ) )  AS DOC_VALUE_NUM,

if ( wildmatch(right(DOC_VALUE, 1 ) , '1','2','3','4','5','6','7','8','9','0') > 0 ,

              MID(   DOC_VALUE, 1  ,     (len(DOC_VALUE)- len(SubField(DOC_VALUE, ' ' , -1 )))

 )  AS DOC_VALUE_STRING

Saludos!!!

View solution in original post

5 Replies
jrepucci15
Creator
Creator

Try something like:
Trim(SubField(String, ' ', -1))

The Subfield with a -1 will get the first field from the right side and the trim gets rid of any spaces.
You might have to put an if Len stmt around that to filter out the ones w/out numbers.

lockematthewp
Creator II
Creator II

Maybe like this:

if (IsNum(SubField([DOC_VALUE_CLEAN], 'for the following number of days ', 2)), SubField([DOC_VALUE_CLEAN], 'for the following number of days ', 2), '-') as [DOC_VALUE_CNT]

if (IsNum(SubField([DOC_VALUE_CLEAN], 'for the following number of days ', 2)), SubField([DOC_VALUE_CLEAN], 'for the following number of days ', 1) & 'for the following number of days', [DOC_VALUE_CLEAN]) as [DOC_VALUE_PARSE]

 

cbaqir
Specialist II
Specialist II
Author

Not every value contains 'for the following number of day'

lockematthewp
Creator II
Creator II

It should still work. If that phrase does not exist, then the whole text is the first subfield.

Marcos_rv
Creator II
Creator II

try this:

if ( wildmatch(right(DOC_VALUE, 1 ) , '1','2','3','4','5','6','7','8','9','0') > 0 , SubField(DOC_VALUE, ' ' , -1 ) )  AS DOC_VALUE_NUM,

if ( wildmatch(right(DOC_VALUE, 1 ) , '1','2','3','4','5','6','7','8','9','0') > 0 ,

              MID(   DOC_VALUE, 1  ,     (len(DOC_VALUE)- len(SubField(DOC_VALUE, ' ' , -1 )))

 )  AS DOC_VALUE_STRING

Saludos!!!