topic using two characters as delimiters in subfield in QlikView

using two characters as delimiters in subfield

diwaskarki — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: using two characters as delimiters in subfield

sunny_talwar — Wed, 08 Aug 2018 17:08:03 GMT

May be just use this

SubField(FieldName, ',') as FieldName

Re: using two characters as delimiters in subfield

diwaskarki — Wed, 08 Aug 2018 17:34:23 GMT

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.

Re: using two characters as delimiters in subfield

sunny_talwar — Wed, 08 Aug 2018 17:40:41 GMT

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

];

Re: using two characters as delimiters in subfield

diwaskarki — Wed, 08 Aug 2018 20:26:36 GMT

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

Re: using two characters as delimiters in subfield

sunny_talwar — Thu, 09 Aug 2018 02:52:29 GMT

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

Re: using two characters as delimiters in subfield

diwaskarki — Tue, 14 Aug 2018 02:30:40 GMT

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.

Re: using two characters as delimiters in subfield

sunny_talwar — Tue, 14 Aug 2018 19:39:19 GMT

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

Re: using two characters as delimiters in subfield

diwaskarki — Tue, 14 Aug 2018 20:50:42 GMT

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.