Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ivanaksen
New 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

Re: How to take First Value before Certain Flag

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)
ivanaksen
New Contributor III

Re: How to take First Value before Certain Flag

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

Re: How to take First Value before Certain Flag

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)
ivanaksen
New Contributor III

Re: How to take First Value before Certain Flag

Thank you 🙂

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

Re: How to take First Value before Certain Flag

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)