Keep the last value of a field line by line

[Eugene81]

Hello Everyone,

I have a case where I have a list of products number with characteristics, their value, and Num_change field that count the modification, I want to keep the data for the last value of this field.

The data looks like this :

Product  Characteristics Value      Num_change
137060   Color                   Red        002
137060   Color                   Greeen  001
137060   Size                     L            002
137060   Size                    M            001

I want to keep this :

Product  Characteristics Value      Num_change
137060   Color                   Red        002
137060   Size                     L            002

according to the last value of Num_change (002) Caracteristics by Characteristics.

I have tried lots of option, FirstSortedValue, Max and Peek with no results at all.

Do you have any idea ?

Thanks in advance

My initial script looks like this:
LIST:
LOAD
  Product,
  Characteristics,
  Value,
  Num_change
FROM [LIB://ODS/Product.qvd] (qvd);

NoConcatenate

LIST_SORTED:
LOAD
  Product,
  Characteristics,
  Value,
  Num_change
RESIDENT LIST
ORDER BY Product, Characteristics, Num_change DESC;

DROP TABLE LIST;

[jpenuliar]

Autonumber should do the trick:

A:
Load * Inline [
Product , Characteristics ,Value , Num_change
137060 , Color , Red , 002
137060 , Color , Greeen , 001
137060 , Size , L , 002
137060 , Size , M , 001
];

NoConcatenate
Load *
Where rownum = 1;
Load *,
AutoNumber(Product&Num_change,Characteristics) as rownum
Resident A
Order by Product,Characteristics Desc;

Drop Table A;

[Eugene81 · Accepted solution]

Hello jpenuliar,

It works perfectly.

Thank you very much

[morgankejerhag]

Or just

A:

Load * Inline [

Product , Characteristics ,Value , Num_change

137060 , Color , Red , 002

137060 , Color , Greeen , 001

137060 , Size , L , 002

137060 , Size , M , 001

];

inner join

Load 

    Product, 

    Characteristics, 

    max(Num_change) as Num_change 

resident A group by Product, Characteristics;

[Eugene81 · Accepted solution]

Hello #morgankejerhag

Thank you very much, that's even better

Regards