topic Re: How to take LastValue data only for certain range in QlikView

How to take LastValue data only for certain range

ivanaksen — Sat, 16 Nov 2024 21:53:55 GMT

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.

Re: How to take LastValue data only for certain range

sunny_talwar — Wed, 14 Nov 2018 12:30:41 GMT

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;

Re: How to take LastValue data only for certain range

ivanaksen — Wed, 14 Nov 2018 13:28:53 GMT

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?

Re: How to take LastValue data only for certain range

sunny_talwar — Wed, 14 Nov 2018 13:44:38 GMT

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

Re: How to take LastValue data only for certain range

ivanaksen — Wed, 14 Nov 2018 13:47:18 GMT

no, no

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

Re: How to take LastValue data only for certain range

sunny_talwar — Wed, 14 Nov 2018 13:49:41 GMT

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

Re: How to take LastValue data only for certain range

Thiago_Justen_ — Wed, 14 Nov 2018 13:54:07 GMT

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

Re: How to take LastValue data only for certain range

ivanaksen — Thu, 15 Nov 2018 09:52:50 GMT

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