Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gireesh1216
Creator II
Creator II

How get the =Y from the comment field

Hello Team,

How to get the =Y combination comment from the below comment field.

Comment:

AAAA=N GGG=Y HH UU II =N GGGHH HH=Y

RRR=Y

GHHG=N HJH=Y

Output:

GGG=Y GGGHH HH=Y

RRR=Y

HJH=Y

 

Please suggest.

 

 

 

 

 

 

Labels (1)
1 Solution

Accepted Solutions
Or
MVP
MVP

This is a bit of an unholy mess and I'm fairly certain there's better ways to get it done, but hey, I think it works. I broke it down into subfields based on the = sign (leaving a '!' in place of any =Y values and | as the separator, you can change those characters if it can show up in your text), then put them back together filtering only the =Y values grouped by the original row.

 

Load OriginalComment, Trim(Concat(CommentSub,' ')) as Comment
Where Right(CommentSub,2) = '=Y'
Group by OriginalComment, Row;
Load OriginalComment, Row, Replace(Subfield(Comment,'|'),'!','=Y') as CommentSub;
Load Comment as OriginalComment, Replace(Replace(Comment,'=Y','!|'),'=N','|') as Comment, RecNo() as Row INLINE
[
Comment
AAAA=N GGG=Y HH UU II =N GGGHH HH=Y
RRR=Y
GHHG=N HJH=Y
];

View solution in original post

7 Replies
Mark_Little
Luminary
Luminary

Hi @gireesh1216 

I would look at doing this in the script, 

I would look at splitting the field values out with the Subfield functions.

Then Filter where RIGHT(Field,2) = '=Y'

Then if needed Concat the fields back together.

sidhiq91
Specialist II
Specialist II

@gireesh1216  Please use below script Editor.

NoConcatenate
Temp:
Load
Comment,
Subfield(Comment,' ') as New_Comment

Inline [
Comment

AAAA=N GGG=Y HHUUII=N GGGHHHH=Y

RRR=Y

GHHG=N HJH=Y
];

 

NoConcatenate
Temp1:
Load if(right(New_Comment,1)='Y',New_Comment,null()) as New_Comment1,
New_Comment,
Comment;

Load Comment,
New_Comment
Resident Temp;
Drop table Temp;
exit script;

In the front end: =Aggr(Concat(New_Comment1,','), Comment)

sidhiq91_0-1659530848645.png

this code only if there is not between words: Like if u see my Inline statement

gireesh1216
Creator II
Creator II
Author

AAAA=N GGG=Y HH UU II =N GGGHH HH=Y

In the above comment "GGGHH HH=Y " having space between two words. The above logic gives  HH=Y only and missing GGGHH word. We need all the text belongs to Y [GGGHH HH=Y]

Or
MVP
MVP

This is a bit of an unholy mess and I'm fairly certain there's better ways to get it done, but hey, I think it works. I broke it down into subfields based on the = sign (leaving a '!' in place of any =Y values and | as the separator, you can change those characters if it can show up in your text), then put them back together filtering only the =Y values grouped by the original row.

 

Load OriginalComment, Trim(Concat(CommentSub,' ')) as Comment
Where Right(CommentSub,2) = '=Y'
Group by OriginalComment, Row;
Load OriginalComment, Row, Replace(Subfield(Comment,'|'),'!','=Y') as CommentSub;
Load Comment as OriginalComment, Replace(Replace(Comment,'=Y','!|'),'=N','|') as Comment, RecNo() as Row INLINE
[
Comment
AAAA=N GGG=Y HH UU II =N GGGHH HH=Y
RRR=Y
GHHG=N HJH=Y
];

sidhiq91
Specialist II
Specialist II

@Or  Thanks much. Learning new things from you.

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

 

NoConcatenate
Temp:
Load
Comment,
Subfield(Comment,'=Y') as New_Comment

Inline [
Comment
AAAA=N GGG=Y HHUUII=N GGGHH HH=Y
RRR=Y
GHHG=N HJH=Y
HJH=Y GHHG=N
];

 

NoConcatenate
Temp1:
Load
Comment,
New_Comment,
If(Index(New_Comment, '=N')=0,
New_Comment,
Mid(New_Comment, Index(New_Comment, '=N')+2, Len(New_Comment)-Index(New_Comment, '=N'))
) as idx
Resident Temp;
Drop table Temp;

 

Aurélien

Help users find answers! Don't forget to mark a solution that worked for you!
Or
MVP
MVP

@sidhiq91  Thanks! In this particular instance, my problem was that using Subfield on the existing strings made it so I could no longer figure out which was =Y and which was =N, so I kept throwing layers on top of it until I could. Whenever I post something like this I'm just waiting for someone like Marcus or Rob or Henric to come in and post a super-elegant solution that's way better than the mess I came up with... but (ugly + works) = good enough most of the time.