Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have 2 tables like below, i want to get record based on latest date. I have to write this in script to load data from SQL.
tab1: | ||
F1 | F2 | F3 |
1 | 01/01/2014 | test1 |
2 | 02/01/2014 | test2 |
2 | 03/01/2014 | test3 |
2 | 04/01/2014 | test4 |
3 | 05/01/2014 | test5 |
3 | 06/01/2014 | test6 |
3 | 07/01/2014 | test7 |
3 | 08/01/2014 | test8 |
4 | 09/01/2014 | test9 |
4 | 10/01/2014 | test10 |
5 | 11/01/2014 | test11 |
Tab 2 | |
F1 | F2 |
1 | Name1 |
2 | Name2 |
3 | Name3 |
4 | Name4 |
5 | Name5 |
Final | |||
F1 | F2 | F3 | F4 |
1 | 01/01/2014 | test1 | Name1 |
2 | 04/01/2014 | test4 | Name2 |
3 | 08/01/2014 | test7 | Name3 |
4 | 10/01/2014 | test10 | Name4 |
5 | 11/01/2014 | test11 | Name5 |
Can any one help?
Tab1:
Load F1,FirstSortedValue(F2,-F2) As F2,FirstSortedValue(F3,-F2) As F3 Group by F1;
Load F1,Date#(F2,'D/M/YYYY') As F2,F3 Inline [
F1,F2,F3
1,01/01/2014,test1
2,02/01/2014,test2
2,03/01/2014,test3
2,04/01/2014,test4
3,05/01/2014,test5
3,06/01/2014,test6
3,07/01/2014,test7
3,08/01/2014,test8
4,09/01/2014,test9
4,10/01/2014,test10
5,11/01/2014,test11 ];
Join
Tab2:
Load * Inline [
F1,F4
1,Name1
2,Name2
3,Name3
4,Name4
5,Name5 ];
There are several ways to solve the problem, via script or via objects expression, I suggest via script:
Load * resident tab1;
left join
Load max(F2) as F2, '1' as flagMax resident tab1 group by F1;
in this way you will have a table with a new field flagMax to be used in your expression
Do now:
left join
load * resident tab2
now you have added Names.
Your expression will use the flag to show only max values
Let me know
hi
try this
Tab1:
Load
F1,
FirstSortedValue(F2,-F2) As F2,
FirstSortedValue(F3,-F2) As F3
Group by F1;
Load
F1,
date(Date#(F2,'D/M/YYYY')) As F2,
F3 Inline [
F1,F2,F3
1,01/01/2014,test1
2,02/01/2014,test2
2,03/01/2014,test3
2,04/01/2014,test4
3,05/01/2014,test5
3,06/01/2014,test6
3,07/01/2014,test7
3,08/01/2014,test8
4,09/01/2014,test9
4,10/01/2014,test10
5,11/01/2014,test11 ];
Join(Tab1)
Tab2:
Load * Inline [
F1,F4
1,Name1
2,Name2
3,Name3
4,Name4
5,Name5 ];
another solution
using peek to get only first F1 (order by F2 desc)
RESULT
SCRIPT
tab1:
load * inline [
F1, F2, F3
1, 01/01/2014, test1
2, 02/01/2014, test2
2, 03/01/2014, test3
2, 04/01/2014, test4
3, 05/01/2014, test5
3, 06/01/2014, test6
3, 07/01/2014, test7
3, 08/01/2014, test8
4, 09/01/2014, test9
4, 10/01/2014, test10
5, 11/01/2014, test11
];
final:
NoConcatenate load
*
Resident
tab1
where
F1 <> peek(F1)
order by F1, F2 desc;
drop table tab1;
tab2:
load F1, F2 as F4 inline [
F1, F2
1, Name1
2, Name2
3, Name3
4, Name4
5, Name5
];