Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
ivanaksen
Contributor III
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

Labels (2)
0 Replies