Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ivanaksen
New Contributor III

Peek function by Flags

Hello everybody,
I have a question, how is possible to use PEEK function to take necessary data.
So I need to take data with TCODE 'ME29N'. And It should be Last TCODE before 'ME23N', 'ME21N' AND 'ME22N'.
So bellow is my Logic for this

Concatenate(CDHDR_TMP)

LOAD
// MANDANT,
// OBJECTCLAS,
OBJECTID,
'CL2' & OBJECTID as CDHDR_KEY,
'CL2' & OBJECTID&UDATE&TCODE as CDHDR_KEY2,
CHANGENR,
USERNAME,
USERNAME as USERNAME_CDHDR,
UDATE,
// 'CL2' & OBJECTID & USERNAME & UDATE as CDHDR_KEY_2,
// UTIME,
TCODE,
TCODE='ME21N' and TCODE='ME29N' as TCODE_TEST,
// PLANCHNGNR,
// ACT_CHNGNO,
// WAS_PLANND,
CHANGE_IND,
// LANGU,
// VERSION,
// _DATAAGING
// AutoNumber(CHANGENR&TCODE&CHANGE_IND) as FLAG_TMP,
TCODE&CHANGE_IND&AutoNumber(CHANGENR&TCODE&CHANGE_IND) as FINAL_FLAG
// TCODE&UDATE as TCODE_IND
;

CDHDR_TMP_3:
Load
CDHDR_KEY2,
Previous(TCODE) as Next_Field
resident CDHDR_TMP
order by CDHDR_KEY2 desc;

left join (CDHDR_TMP)
Load*
resident CDHDR_TMP_3;
drop table CDHDR_TMP_3;

// exit script;

CDHDR_TMP_2:
Load *,
if(CDHDR_KEY=peek(CDHDR_KEY),
if((TCODE='ME21N' and Next_Field='ME29N') or (TCODE='ME23N' and Next_Field='ME29N') or (TCODE='ME22N' and Next_Field='ME29N'),1+peek(Counter),peek(Counter)),1) as Counter
Resident CDHDR_TMP
order by CDHDR_KEY,UDATE;
Drop Table CDHDR_TMP;

After that I am taking Last value of  Date  where TCODE=ME29N and Counter=1

For most of variants this logic is working correctly, And taking Correct ME29N with Counter '1':

2.jpg

But Some of them has the bellow problem, where should be taken Data in blue frame, 
But as you see It`s taken red frame, because it has Counter '1'

1.jpg

Thanks in advance

Tags (2)
Labels (2)