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: 
diwaskarki
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
sunny_talwar

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
sunny_talwar

May be just use this

SubField(FieldName, ',') as FieldName

diwaskarki
Creator II
Creator II
Author

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.

sunny_talwar

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),"

];

diwaskarki
Creator II
Creator II
Author

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

sunny_talwar

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

diwaskarki
Creator II
Creator II
Author

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.

sunny_talwar

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

diwaskarki
Creator II
Creator II
Author

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.