Find value to max(date)

[phil-ebm]

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

[maxgro · Accepted solution]

FirstSortedValue(Value, -Date)

[phil-ebm]

I try this first but it doesn't work

[anbu1984]

When you are loading data in script, change your Date field to date format like

Date#(Date,'D.M.YYYY') As Date

[maxgro]

attachment with your data

[jyothish8807]

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

[phil-ebm]

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