Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
MalcolmCICWF
Creator III
Creator III

SubField problem pulling in data regardless of character

I have a Description field in a table that can have various... well.... descriptions in it. But, there is some useful data in the form of a Name that I would like to extract. For those instances the Description field is prefaced with NAME: and then can have some other data after it. Other Descriptions just have a bunch of data in it.

I am using

Anyone aware of or work with a field where they have encountered this or used SubField? Like... If the field contains ':', use the SubField?

Labels (7)
1 Solution

Accepted Solutions
JGMDataAnalysis
Creator III
Creator III

In your case, the SubField function will return the substring between the first character of the string passed as the first argument and the position of the first occurrence of the colon minus 1. So, if -> '** DISABLED **' <- or -> 'Encrypt a file using QTZ.' <- found to the left of the first colon will be part of the resulting string.

clipboard_image_0.png

View solution in original post

7 Replies
sunny_talwar

I am using? Did you missed something after that?

lockematthewp
Creator II
Creator II

if (subfield([Description], 'NAME:', 2) <> '', subfield([Description], 'NAME:', 2), 'None')

or

if (isNull(subfield([Description], 'NAME:', 2)), 'None', subfield([Description], 'NAME:', 2))

MalcolmCICWF
Creator III
Creator III
Author

SubField(Description, ':', 1) as NewField

MalcolmCICWF
Creator III
Creator III
Author

2 is pulling in after the ':'

I am using Subfield(Description, ':', 1) as Newfield.  This gets me what I need which is whatever name is before the ':', but if the field doesn't contain ':' it pulls everything currently there. I want to omit these and only keep the names.

JGMDataAnalysis
Creator III
Creator III

Try this..
If(Index(Description, ':'), SubField(Description, ':', 1), Null()) AS NewField
MalcolmCICWF
Creator III
Creator III
Author

So this worked thanks, but so did  IF(WildMatch(Description, '*:*')>0,SubField(Description, ':',1),'Other')

The problem with both is there are a couple exceptions that got through and I don't know why. I have a few records that kept the string  -->  **DISABLED**  <--  and then one record that has  -->   Encrypt a file using QTZ.  <--  I don't know if it has to do with the  *  character surrounding the word or the  .  at the end of the other.

Is there a way to take care of these or a reason you can think of they would be included?

JGMDataAnalysis
Creator III
Creator III

In your case, the SubField function will return the substring between the first character of the string passed as the first argument and the position of the first occurrence of the colon minus 1. So, if -> '** DISABLED **' <- or -> 'Encrypt a file using QTZ.' <- found to the left of the first colon will be part of the resulting string.

clipboard_image_0.png