Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
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
mark6505
Partner - Master
Partner - Master

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
Specialist

@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
Specialist

@Or  Thanks much. Learning new things from you.

Aurelien_Martinez
Partner - Specialist
Partner - Specialist

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.