Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to take First Value before Certain Flag

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

1.jpg

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

Labels (3)
5 Replies

May be this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

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??

Highlighted

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';

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

Thank you 🙂

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

Highlighted

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
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)