How get the =Y from the comment field

[gireesh1216]

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.

[Mark_Little]

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]

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

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

[gireesh1216]

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 · Accepted solution]

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]

@Or  Thanks much. Learning new things from you.

[Aurelien_Martinez]

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

[Or]

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