Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I need to find a Value from many rows to a max(date).
I have a table like this:
Teilenr | Date | Value |
---|---|---|
00108-2-0239 | 15.11.2011 | Freigabe |
00108-2-0239 | 22.02.2011 | Freigabe |
00108-2-0239 | 11.03.2010 | einmalige Prüfausnahme |
34134-1-0172 | 17.10.2011 | einmalige Prüfausnahme |
34134-1-0172 | 20.01.2011 | Freigabe |
00322-2-0247 | 22.12.2012 | keine Freigabe |
00322-2-0247 | 01.01.2011 | Freigabe |
Now I want in a other Table for every "Teilenr" one row with the highst date and the Value from this date:
It should look like this:
Teilenr | max(Date) | Value from max(Date) |
---|---|---|
00108-2-0239 | 15.11.2011 | Freigabe |
34134-1-0172 | 17.10.2011 | einmalige Prüfausnahme |
00322-2-0247 | 22.12.2012 | keine Freigabe |
"Teilenr" (dimension) and "max(date)" (expression) works fine but i see no value? How can get a value for the max(date) expression?
Best regards
FirstSortedValue(Value, -Date)
FirstSortedValue(Value, -Date)
I try this first but it doesn't work
When you are loading data in script, change your Date field to date format like
Date#(Date,'D.M.YYYY') As Date
attachment with your data
Hi Philips,
First change your date format to:
Date |
11/15/2011 |
2/22/2011 |
11/3/2010 |
10/17/2011 |
1/20/2011 |
12/22/2012 |
1/1/2011 |
Dimension:
Teilenr
Date
Then in Expression:
firstsortedvalue(Value,-Aggr(Max(Date),Teilenr))
Reagdrs
KC
Oh guys
the problem was in the script.
After I add the ID I see, that the creater of the data-model joined an other table to this data (he says to me, there is only one table) and some rows are duplicated after the join.
after I change the join into keep, there's no problem and now everything works fine
thanks for your help