Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

using two characters as delimiters in subfield

I have a long string that looks like this:

Application Name | (somestring, somestring, somestring ), application name2 | (somestring1, somestring1, somestring1 ), application name3 | (somestring1, somestring1, somestring1 ),

Application Name |(somestring, somestring, somestring ) needs to be in row1

application name2 | (somestring, somestring, somestring ) needs to be row2 and so on.

How can I use the closing parenthesis and comma as a delimiter in the subfield function?

1 Solution

Accepted Solutions
Highlighted

Re: using two characters as delimiters in subfield

How about this

Table:

LOAD *

Where Trim(NewFieldName) <> ')';

LOAD *,

Trim(SubField(FieldName, '),') & ')') as NewFieldName;

LOAD * INLINE [

    FieldName

    "Application, Name | (my entitlement name), application, name2 | (entitlement name 2), application name3 | (entitlement name 4),"

];

View solution in original post

8 Replies
Highlighted

Re: using two characters as delimiters in subfield

May be just use this

SubField(FieldName, ',') as FieldName

Highlighted
Creator II
Creator II

Re: using two characters as delimiters in subfield

Just realized I made an error in my question. I'll make the edit. The string in the paranthesis itself will contain several commas so I cant do that.

Highlighted

Re: using two characters as delimiters in subfield

How about this

Table:

LOAD *

Where Trim(NewFieldName) <> ')';

LOAD *,

Trim(SubField(FieldName, '),') & ')') as NewFieldName;

LOAD * INLINE [

    FieldName

    "Application, Name | (my entitlement name), application, name2 | (entitlement name 2), application name3 | (entitlement name 4),"

];

View solution in original post

Highlighted
Creator II
Creator II

Re: using two characters as delimiters in subfield

Thank you Sunny bhai, it seemed to do the trick. Can you please explain what the WHERE clause does?

Highlighted

Re: using two characters as delimiters in subfield

When you remove the Where clause, you will see that one extra row was generated with ')'... so I removed that using the where clause

Highlighted
Creator II
Creator II

Re: using two characters as delimiters in subfield

Got it. But the problem is I have one more field in the same table where I use subfield function. I think because of the where clause, the subfield function on that field does not work. Is there a way to do a where for this field also. I am using ',' as a delimiter on this one.

Highlighted

Re: using two characters as delimiters in subfield

Would you be able to share an updated sample where we can see the problem?

Highlighted
Creator II
Creator II

Re: using two characters as delimiters in subfield

Load

Where TRIM([Separate ASAR Entitlements]) <> ')' & Trim([Separated Removed Roles]);

Trim(Subfield([New Removed Entitlements],'),') &')') As [Separate ASAR Entitlements],

Trim(Subfield([New Removed Roles],',')) AS [Separated Removed Roles],

This is a snippet of my script.

Because of that where clause the second subfield function does not work.