topic Reduce table output based on latest Purchasing date in QlikView

Reduce table output based on latest Purchasing date

Tue, 26 Jun 2012 11:02:53 GMT

Hi all,

I'm having problems getting our app to work based on the following requirement:

- from a table with several output records per material, get only one record per material based on its max Purchasing Date.

For example, based on the following entry data:

Center	Material ID	Material Type	Purchasing Date	Class
A001	Mat01	TA	01/01/2012	1
A001	Mat01	TA	26/02/2012	1
A001	Mat02	TB	10/01/2012	1
A002	Mat01	TA	30/01/2012	1
A002	Mat03	TC	23/04/2012	1
A002	Mat03	TC	30/04/2012	2

we would like to have a single output record per Center/Material, taking as result value the one with latest Purchasing Date in case of several entry records:

Desired Output:

A001	Mat01	TA	26/02/2012	1
A001	Mat02	TB	10/01/2012	1
A002	Mat01	TA	30/01/2012	1
A002	Mat03	TC	30/04/2012	2

Thanks in advance for your help,

Best regards,

Enric

Re: Reduce table output based on latest Purchasing date

Miguel_Angel_Baeyens — Tue, 26 Jun 2012 11:09:47 GMT

Hi Enric,

If you create a chart with Center, Material ID, Material Type and Purchasing Date as dimensions, the expression should be like

FirstSortedValue(Class, -[Purchasing Date])

Hope that helps.

Miguel

Re: Reduce table output based on latest Purchasing date

Tue, 26 Jun 2012 15:30:06 GMT

Thanks, Miguel,

That was exactly what I was looking for.

Best regards!

Enric