topic Re: Find value to max(date) in QlikView

Find value to max(date)

phil-ebm — Fri, 10 Oct 2014 07:55:44 GMT

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

Re: Find value to max(date)

maxgro — Fri, 10 Oct 2014 08:00:22 GMT

FirstSortedValue(Value, -Date)

Re: Find value to max(date)

phil-ebm — Fri, 10 Oct 2014 08:21:00 GMT

I try this first but it doesn't work

Re: Find value to max(date)

anbu1984 — Fri, 10 Oct 2014 08:26:00 GMT

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

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

Re: Re: Find value to max(date)

maxgro — Fri, 10 Oct 2014 08:27:52 GMT

attachment with your data

Re: Find value to max(date)

jyothish8807 — Fri, 10 Oct 2014 08:40:24 GMT

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

Re: Find value to max(date)

phil-ebm — Fri, 10 Oct 2014 09:10:55 GMT

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