topic Re: Showing the latest record in QlikView

Showing the latest record

Tue, 12 Feb 2013 12:30:51 GMT

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

Re: Showing the latest record

alexandros17 — Tue, 12 Feb 2013 12:34:38 GMT

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

Re: Showing the latest record

Gysbert_Wassenaar — Tue, 12 Feb 2013 12:36:43 GMT

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.

Re: Showing the latest record

Tue, 12 Feb 2013 12:57:58 GMT

Thanks,

unfortunately some of my records have same dates.

Re: Showing the latest record

alexandros17 — Tue, 12 Feb 2013 13:08:56 GMT

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

Re: Showing the latest record

Wed, 13 Feb 2013 06:29:48 GMT

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