Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Discover how organizations are unlocking new revenue streams: Watch here
cancel
Showing results for 
Search instead for 
Did you mean: 
Eugene81
Contributor II
Contributor II

Keep the last value of a field line by line

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;

 

 

 

 

 

 

Labels (1)
2 Solutions

Accepted Solutions
Eugene81
Contributor II
Contributor II
Author

Hello jpenuliar,

It works perfectly.

Thank you very much

View solution in original post

Eugene81
Contributor II
Contributor II
Author

Hello #morgankejerhag

Thank you very much, that's even better

Regards

View solution in original post

4 Replies
jpenuliar
Partner - Specialist III
Partner - Specialist III

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
Contributor II
Contributor II
Author

Hello jpenuliar,

It works perfectly.

Thank you very much

morgankejerhag
Partner - Creator III
Partner - Creator III

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
Contributor II
Contributor II
Author

Hello #morgankejerhag

Thank you very much, that's even better

Regards