Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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?

1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

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
Highlighted

I am using? Did you missed something after that?

Highlighted
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))

Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

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.

Highlighted
Creator II
Creator II

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

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?

Highlighted
Creator II
Creator II

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