Showing the latest record

[#1]

Hi

I want to display the customer record with the latest date on a chart/table.Please assist.

e.g

CustomerID	Address1	Address2	City	Code	Address_Change_Date
123	12 kloof rd	Newcastle	New Zealand	26541	01/01/2012
123	5 Frey Street	Bradys	Toronto	000123	06/05/2012
123	45 Lombardy road	Lombardy	Kimberly	56230	01/02/2013
46	20 Leeds Street	Faya	Brazil	569874	05/08/2011
46	12 Gorg rd	Merid	Canada	69656	01/05/2012

[alexandros17]

If you want just one value (the max) use Max in your expression or when you load data.

[Gysbert_Wassenaar]

Use the firstsortedvalue function. See attached example. You'll have to make sure no customer id's have two records for the same date though or firstsortedvalue will return a null. So if you have a customer that changes addresses twice (or more) on the same day you're out of luck. There's no way to determine which is the record the right one then.

[#4]

Thanks,

unfortunately some of my records have same dates.

[alexandros17]

when you load data, let's suppose that the name of the field is myData, add

....

left join

Select

     Key,

     max(myData) as Maxim

from myTable

where Key is the key of the table from wich you actually exctract the field data

In this way your original table ill have a new colunmn containing always max value.

This metod let you compute even other dates for examnple by year

[#6]

Same dates for one customer and the dates do not have time.That is why it showing nothing with the firstsortedvalue function.