Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello guys,
i'm newbie in qlik sense, I would like to select the last value in a column of my table, I don't care if it is the lowest or the highest (simply the last DELAY column value of the last row).
Example of my table:
ID| DATE1| DATE2| DELAY| VERSION
1| date1| date2 | 5 | 1
1| date1| date2 | 6 | 2
2| date1| date2 | 3 | 1
2| date1| date2 | 2 | 2
desired output:
for ID=1 select DELAY = 6
for ID=2 select DELAY=2
My code in measure expression in a another table:
aggr(
if (
date(max(DATE2)) > date(max(DATE1))
//then
,date(max(DATE2)) - date(max(DATE1)),
//else
{<VERSION=max(VERSION)>}DELAY
)
, [ID])
How can i fix it? thanks all
Your requirement seems to be quite confusing but you can do it either way
Load ID,
max(delay) as Max_delay
Group by ID
Resident Tablename;
If you want the last value, use it like this.
Load ID,
peek('delay', -1, 'Tablename') as lastvalue,
peek('delay', 0, 'Tablename') as firstalue,
Resident Tablename;
In Chart use below
Dimension
=ID
Measure
= firstsortedvalue(DELAY,-date2) // delay for latest date2
= firstsortedvalue(DELAY,date2) // delay for oldest date2
If you just want last row for each ID, then you would need to create a new Autonumber() field while loading the data as below
Main:
Load ID,Date1,Date2,Delay,Version
,Autonumber(ID) as RecordByID
From SomeSource;
Then in charts use
Dimension
=ID
Measure
=FirstSortedValue(DELAY,-RecordByID) // True last row for each ID
Maybe you can also leverage VERSION field
=FirstSortedValue(DELAY,-VERSION) // delay for latest version