subfield using last instance of a character

[diwaskarki]

Hello guys,

Im bringing in a list of strings from an xml . It looks something like this.

B22  AD Production | (first set) (cn=last set of paranthesis)

B22  AD Dev | (Only one set of parenthesis)

I am trying to do a subfield  to do a line break after the last ). My end goal is to bring each line as a new line.  So, how do I use subfield to do this or is there a better way of separating this lines in to new lines of data? I would really appreciate the help.

Edit: I have included additional information.

This is my script that brings whats to bring that data.

IF (ACTION = 'Remove Group Access', TextBetween(Replace(Replace(Replace(Replace(ATTRIBUTES,chr(10),' '),' ', ''), '<String>', ''), '</String>', chr(10)), '<entrykey="RemovedAccess"><value><List>', '</List>')) As [Removed Entitlements],

So , essentially I am replacing the end string tag with a line break. But all those line are coming in as 1 count of data, even if there is a line break. I want to bring those as separate lines. So when I do a count on removed entitlements, each line of that data will be counted as its own. In this case count([Removed Entitlements]) should be 3. Hope this clarifies things.

EDIT: After using subfield.

In the ASAR Revocation Table, if you look at D8VA entitlements [Removed Entitlements] , I have two rows of data. One has a string and another is blank. I did a subfield of that which is displayed in the table below. D8VA now have an additional data. The one ending in TestRBAC5. I dont know where that is coming from.

[sunny_talwar]

How are you expecting the output of these two lines to look like?

[diwaskarki]

Sunny,

I don't want any change in the format of the output. I just want those two statements in new line. Currently , the way they are stored in the xml and the way Im extracting it in qlikview. Its coming as a single line or rather single count. Even if they have a line break.

When I do a count of the field containing this data, the result is 1 instead of 2. Sorry, if I am sounding confusing.

[aarkay29 · Accepted solution]

Try this   Count(NewField)

LOAD *,

Subfield([Removed Entitlements],chr(10)) as [NewField];

LOAD

*,

IF (ACTION = 'Remove Group Access', TextBetween(Replace(Replace(Replace(Replace(ATTRIBUTES,chr(10),' '),' ', ''), '<String>', ''), '</String>', chr(10)), '<entrykey="RemovedAccess"><value><List>', '</List>')) As [Removed Entitlements]

From

Table;

[diwaskarki]

Aar Kay,

Im marking your answer is correct because in usual circumstances your solution should do the trick.

I am storing the new field in [Separated Entitlements]In my case I think it partially did the job. However, when I select a field I have in a listbox which is from the same table where I have the new field [Separated Entitlements] , it shows an additional data in the table box where I am displaying [Separated Entitlements.]

However, if I select one more field(which is also from the same table) from a different listbox , data is accurate.

Can you think of why it would behave in such a strange way? Thanks for your help. 

[aarkay29]

Diwas,

Not really sure of the what is going on in your app. But if you can share the sample, I might be able to help because when you say additional data I have no clue where it is coming from or what it is.

[diwaskarki]

I just included more info on the issue.

[aarkay29]

I am assuming you are not doing joins here.

From what I am seeing, I am guessing that may be for D8VA -there might be TestRBAC5 in the next line and you are not able to see in the table ....!