Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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
Highlighted

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;
Highlighted
Contributor III
Contributor III

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?

 

Highlighted

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

Highlighted
Contributor III
Contributor III

no, no

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

 

Highlighted

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

Highlighted
Specialist III
Specialist III

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

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