topic Re: Checking for duplicate primary keys and only displaying recent dates in QlikView

Checking for duplicate primary keys and only displaying recent dates

Tue, 02 Apr 2013 09:30:29 GMT

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.

Tue, 02 Apr 2013 09:34:17 GMT

Hi,

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

Regards,

Amay

kaushiknsolanki — Tue, 02 Apr 2013 09:37:36 GMT

Hi,

You can load the data like this.

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

Regards,

Kaushik Solanki

Tue, 02 Apr 2013 09:50:52 GMT

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 — Tue, 02 Apr 2013 09:55:46 GMT

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

Tue, 02 Apr 2013 10:08:52 GMT

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

This forum is insanely responsive