How to take First Value before Certain Flag

[ivanaksen]

Hello Everybody,

Please support with bellow problem.
I need to take first User and Date(green frame), before certain flag (the flag is  concatenation of values and marked by blue frame). 

CDHDR_TMP:

LOAD
OBJECTID,
'CL1' & OBJECTID as CDHDR_KEY,
CHANGENR,
USERNAME,
UDATE,
TCODE,
CHANGE_IND,
TCODE&CHANGE_IND&AutoNumber(CHANGENR&TCODE&CHANGE_IND) as FINAL_FLAG
(qvd)
where USERNAME<>'RPAPRD1';

CDHDR:
Load *,
Left(FINAL_FLAG,6)='ME21NU' as Flag
Resident CDHDR_TMP;
Drop Table CDHDR_TMP;

So, please support, what formula should I use and how? 

Thanks a million in advance

[Anil_Babu_Samineni]

May be this?

If(Left(FINAL_FLAG,6)='ME21NU',FINAL_FLAG) as Flag

[ivanaksen]

Thanks for your answer 🙂

But it will just show the concatenation data FINAL_FLAG.
How to take the date (UDATE) and user name (USERNAME) which is going before the ME21NU??

[Anil_Babu_Samineni]

Instead you can simply define like

CDHDR_TMP:

Load *,  If(Left(FINAL_FLAG,6)='ME21NU', FINAL_FLAG) as Flag,

LOAD
OBJECTID,
'CL1' & OBJECTID as CDHDR_KEY,
CHANGENR,
USERNAME,
UDATE,
TCODE,
CHANGE_IND,
TCODE&CHANGE_IND&AutoNumber(CHANGENR&TCODE&CHANGE_IND) as FINAL_FLAG,

/*If(Left(TCODE&CHANGE_IND&AutoNumber(CHANGENR&TCODE&CHANGE_IND))='ME21NU', TCODE&CHANGE_IND&AutoNumber(CHANGENR&TCODE&CHANGE_IND)) as FLAG*/
(qvd)
where USERNAME<>'RPAPRD1';

[ivanaksen]

Thank you 🙂

But how can I take the necessary fields, just before these FLAGS? 

[Anil_Babu_Samineni]

I can recommend to use left join to load necessary fields. But, I would think
1. Load only those needs
2. Else drop which you dont want