Checking for duplicate primary keys and only displaying recent dates

[#1]

Hey,

This is kind of a double header.

Say I've got a customer account, with a relevant primary key (A customer ID). This customer then closes the account, but later reopens.

I keep a record of all this and when it happens.

Lets say the customer ID is 25.

So now I have a record involving:

25 - Open - January 12th 2012

25 - Closed - August 25th 2012

25 - Reopen - January 15th 2013

I only want to display/load the most recent status of that customers account (The reopen on Jan 15th).

How should I go about this?

An if loop in the script, some expression in a chart?

Thanks for any help,

Joe.

[#2]

Hi,

Try using LastValue() function. That might help in your case

Regards,

Amay

[kaushiknsolanki]

Hi,

     You can load the data like this.

     Load Customer_id, Max(Date) as Date from xyz group by Customer_id;

Regards,

Kaushik Solanki

[#4]

I'm going to out myself as a bit of an idiot here, but what would xyz group refer to in this case?

I'm using an access database via ODBC if this changes the syntax at all?

And would I put that on the line where I would usually load the field Customer, or seperately from it?

Thanks for anymore help.

[kaushiknsolanki · Accepted solution]

Hi,

     In my example XYZ is nothing but the table from which you will be fetching the data.

     If you are trying to get the data from database, Try below Script.

     Sql Select Customer_id, Max(Date) As Date from Table_Name Group by Customer_id;

     Here Date field is the Field where you have Dates in record.

Regards,

Kaushik Solanki

[#6]

Amazing, thank you for the swift and well explained help.

This forum is insanely responsive