Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello together,
I need your help. I have the following function:
Max(Datenpunkt_ID)
it´s show me the correct answer with 214.
If I use the function:
Max(Datenpunkt_ID)-10
It´s show me the answer 204, thats correct
Now I need a function that I can delete the 10 high values, because my Datenpunkt_ID table look like this:
Datenpunkt_ID
214
213
212
211
210
209
208
207
Has anybody an idea?
Not sure what you mean with '10 high values' with regard to your calculation of Max(Datenpunkt_ID)-10, but maybe something like this?
DP:
LOAD * INLINE [
203
201
200
];
LEFT JOIN
LOAD Max(Datenpunkt_ID)-10 as MaxID
Resident DP;
Final:
LOAD Datenpunkt_ID
Resident DP
Where Datenpunkt_ID < MaxID;
DROP TABLE DP;
I need the result of 209. So I look for a function with a resolve.
The 10 highest max values should not be considered
i think you should Change this part from
//that will exclude the ID's > 204, which dont have to be the last 10 values
to
LOAD Max(Datenpunkt_ID, 10) as MaxID
//this will get you the real last 10 max values
I think Max(Datenpunkt_ID,10) won't consider duplicates correctly.
Try
First 1 LOAD Datenpunkt_ID as MaxID
WHERE Recno() >= 10
ORDER BY Datenpunkt_ID desc
;
Stefan your answer look correct with recno>=10
but how I can use the function in KPI
And Tim with your function I get the wrong answer, with duplicates include.
Maybe like
=Max( Aggr( If(Rangesum(Above(Count(Datenpunkt_ID),0,Rowno())) >=10, Datenpunkt_ID), Datenpunkt_ID))
This assumes Datenpunkt_IDs are loaded in order desc.
If this is not the case, you would need to create a correct load order in the script or use sorted aggr() dimension.
The sortable Aggr function is finally here!
Instead of the Rangesum(Above(...)) part, you can also create a record counter in the resident table using Recno() and check against this field.
