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(PO_Appr.Date_TMP) as Last_PO_Apprv_Date,
LastValue(USERNAME) as Last_PO_Approver
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.
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;
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?
Try this out:
If(Previous(CDHDR_KEY)<>CDHDR_KEY,USERNAME) as Last_PO_Approver_Temp
Resident CDHDR_FINAL Order by PO_Appr.Date_TMP;
FirstSortedValue(Last_PO_Approver_Temp , PO_Appr.Date_TMP) as Last_PO_Approver
Resident LastValue_Temp Group By CDHDR_KEY;
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:
If(If(FirstValue(Left(FINAL_FLAG,6)='ME21NU'),FINAL_FLAG-'1'),1,0) as Indicator
drop table CDHDR_TMP;
But the problem that Qlik is telling that "Invalid Expression". Can you advise what`s the problem?
Thanks a lot