Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

subfield using last instance of a character

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.

sample data.png

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.


messed up data.png

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.

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Re: subfield using last instance of a character

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;

View solution in original post

7 Replies
Highlighted

Re: subfield using last instance of a character

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

Highlighted
Creator II
Creator II

Re: subfield using last instance of a character

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.

Highlighted
Specialist
Specialist

Re: subfield using last instance of a character

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;

View solution in original post

Highlighted
Creator II
Creator II

Re: subfield using last instance of a character

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. 

Highlighted
Specialist
Specialist

Re: subfield using last instance of a character

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.

Highlighted
Creator II
Creator II

Re: subfield using last instance of a character

I just included more info on the issue.

Highlighted
Specialist
Specialist

Re: subfield using last instance of a character

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 ....!