Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
ivanaksen
New Contributor III

How to take LastValue data only for certain range

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". 

 1.jpg

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.

Labels (4)
7 Replies

Re: How to take LastValue data only for certain range

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

Re: How to take LastValue data only for certain range

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

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

ivanaksen
New Contributor III

Re: How to take LastValue data only for certain range

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

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

thiago_justen
Valued Contributor III

Re: How to take LastValue data only for certain range

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

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
ivanaksen
New Contributor III

Re: How to take LastValue data only for certain range

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