Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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;
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
I'm really sorry, I didn't mark the solution as valid, but works fine!
Thanks