Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Gabri
Contributor III
Contributor III

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

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

 

DATEAPLICATIONCUSTOMERTYPENUMBERUSER
02/05/2018RESIDENTIALAAAArolling10000JB
02/05/2018INDUSTRIALAAAArolling2000JB
02/05/2018INDUSTRIALAAAAsectional3000JB
02/12/2018RESIDENTIALAAAArolling99999JB
02/12/2018INDUSTRIALAAAArolling33333JB
02/12/2018INDUSTRIALAAAAsectional66666JB

 

The desired output should be:

02/12/2018RESIDENTIALAAAArolling99999JB
02/12/2018INDUSTRIALAAAArolling33333JB
02/12/2018INDUSTRIALAAAAsectional66666JB

 

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

Thanks,

Labels (1)
1 Solution

Accepted Solutions
Arthur_Fong
Partner
Partner

Try this:

MC.PNG

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;

View solution in original post

3 Replies
Arthur_Fong
Partner
Partner

Try this:

MC.PNG

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;

View solution in original post

Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Gabri
Contributor III
Contributor III
Author

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

 

test.JPG

 

Thanks