How to take LastValue data only for certain range

[ivanaksen]

Hello everybody,
Please support with bellow question:
It`s necessary to take "lastvalue" in the certain range of data.
The main idea of this range, is to take LastValue ONLY for the first range(green frame on the print screen bellow), the main indicator which is starting new range is TCODE "ME21N". 

 

Before I had bellow formula in the script, But it takes only the very last value (the red frame with "17.10.2018" from print screen).:

LastValue:
LOAD CDHDR_KEY,
LastValue(PO_Appr.Date_TMP) as Last_PO_Apprv_Date,
LastValue(USERNAME) as Last_PO_Approver
Resident CDHDR_FINAL
Group By CDHDR_KEY
Order by PO_Appr.Date_TMP;

Can you advise, how can use above formula, but with indicator: "to take data only before next "ME21N"?

Thanks a lot in advance.

[sunny_talwar]

May be this

LastValue:
LOAD CDHDR_KEY,
   Max(PO_Appr.Date_TMP) as Last_PO_Apprv_Date,
   FirstSortedValue(USERNAME, -PO_Appr.Date_TMP) as Last_PO_Approver
Resident CDHDR_FINAL
Group By CDHDR_KEY;

[ivanaksen]

Hello,

Thank you, but unfortunately it`s not working,
maybe something with  functions Previous?
IF(x=Previous(x) AND y=Previous(y),'0','1') AS COUNT_FLAG,
if(z<Previous(z) and x=Previous(x),'1','0') as Vizualization_Flag?

[sunny_talwar]

Previous function? But I don't even have Previous in the code that I shared? I am confused... can you elaborate?

[ivanaksen]

no, no

Command with the name "Previous". I attached in the previous message an example of such command.

[sunny_talwar]

How is this new code you shared linked to what you initially provided?

[Thiago_Justen_]

Try this out:

LastValue_Temp:

LOAD

         CDHDR_KEY,

        PO_Appr.Date_TMP,

       If(Previous(CDHDR_KEY)<>CDHDR_KEY,USERNAME) as Last_PO_Approver_Temp

Resident CDHDR_FINAL Order by PO_Appr.Date_TMP;

LastValue:

LOAD

        CDHDR_KEY,

        FirstSortedValue(Last_PO_Approver_Temp , PO_Appr.Date_TMP) as Last_PO_Approver

Resident LastValue_Temp Group By CDHDR_KEY;

Cheers

[ivanaksen]

Hello, thanks a lot for your answer, but it`s not working correctly.

I had another option, to make flag:
text(TCODE&CHANGE_IND&AutoNumber(CHANGENR&TCODE&CHANGE_IND)) as FINAL_FLAG

And to make such formula:
CDHDR_TMP2:
load*,
If(If(FirstValue(Left(FINAL_FLAG,6)='ME21NU'),FINAL_FLAG-'1'),1,0) as Indicator
resident CDHDR_TMP;
drop table CDHDR_TMP;
But the problem that Qlik is telling that "Invalid Expression". Can you advise what`s the problem?

Thanks a lot