Catch only the last value of multiple fields by the last date

[Gabri]

Hi, I want to show a table filtered with the last value entered by date, for example, I have this table:

DATE	APLICATION	CUSTOMER	TYPE	NUMBER	USER
02/05/2018	RESIDENTIAL	AAAA	rolling	10000	JB
02/05/2018	INDUSTRIAL	AAAA	rolling	2000	JB
02/05/2018	INDUSTRIAL	AAAA	sectional	3000	JB
02/12/2018	RESIDENTIAL	AAAA	rolling	99999	JB
02/12/2018	INDUSTRIAL	AAAA	rolling	33333	JB
02/12/2018	INDUSTRIAL	AAAA	sectional	66666	JB

The desired output should be:

02/12/2018	RESIDENTIAL	AAAA	rolling	99999	JB
02/12/2018	INDUSTRIAL	AAAA	rolling	33333	JB
02/12/2018	INDUSTRIAL	AAAA	sectional	66666	JB

In conclusion, each time that fields (APLICATION, TYPE and USER) are equal, I need to catch the last (DATE) and print (NUMBER)

Thanks,

[Arthur_Fong · Accepted solution]

Try this:

Script:

Raw:
load * inline [
DATE	APLICATION	CUSTOMER	TYPE	NUMBER	USER
02/05/2018	RESIDENTIAL	AAAA	rolling	10000	JB
02/05/2018	INDUSTRIAL	AAAA	rolling	2000	JB
02/05/2018	INDUSTRIAL	AAAA	sectional	3000	JB
02/12/2018	RESIDENTIAL	AAAA	rolling	99999	JB
02/12/2018	INDUSTRIAL	AAAA	rolling	33333	JB
02/12/2018	INDUSTRIAL	AAAA	sectional	66666	JB
](delimiter is '	');

Data:
load *,if(RowNo()=1,1,if(Key=peek(Key),1+peek(Flag),1))AS Flag;
load DATE#(DATE,'DD/MM/YYYY')as DATE,APLICATION,USER,TYPE,CUSTOMER,NUMBER,
APLICATION&'|'&USER&'|'&TYPE AS Key
resident Raw
order by APLICATION,USER,TYPE, DATE desc;

DROP TABLE Raw;

NoConcatenate
Data2:
load * 
resident Data
where Flag=1;

drop table Data;

exit script;

[Brett_Bleess]

Did Arthur's proposed solution for your use case work for you?  If so, do not forget to return to your post and use the Accept as Solution button on Arthur's post to give him credit for the assistance and let other Community Members know things worked for you.  If you still require further assistance, please leave an update.

I am going to post the base URL for the Design Blog area as well, you will find hundreds of how-to related posts in this area that may be helpful in the future, these are all written by our internal experts.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

[Gabri]

I'm really sorry, I didn't mark the solution as valid, but works fine!

Thanks