Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
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.
@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
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]
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
];
@Or Thanks much. Learning new things from you.
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
@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.