Loading data - Grouping/aggregation issue

[summerrain]

Hi,

Looks simple aggregation, but I am stuck on this for some reason.

I would like to load from this table

Emp	Date	Status	Sequence
A0	2018-12-03	A	0
A1	2015-05-03	A	0
B1	2019-10-20	A	0
B1	2019-10-20	I	1
B1	2019-10-21	I	0

And  get results like this, so only rows with highest sequance are loaded:

Emp	Date	Status
A0	2018-12-03	A
A1	2015-05-03	A
B1	2019-10-20	I
B1	2019-10-21	I

Tried this didn't work...

Temp:
LOAD * INLINE [
Emp, Date, Status, Sequence
A0, 2018-12-03, A, 0
A1, 2015-05-03, A, 0
B1, 2019-10-20, A, 0
B1, 2019-10-20, I, 1
B1, 2019-10-21, I, 0
];

Main:
Load

Emp,
Date,
Status,
Max(Sequence)

resident Temp

group by Emp, Date, Status;

drop table Temp;

[zhadrakas]

that works correct.
B1, 2019-10-20, A, 0
B1, 2019-10-20, I, 1
B1, 2019-10-21, I, 0
Cause you group by Emp, Date, Status that one line will not dissapear cause its a different status.
Maybe you need to remove Status from the second load.

[summerrain]

Well, but I need to get that status and that it would be for the highest sequence of that date.

[zhadrakas · Accepted solution]

try that:

Temp:
LOAD * INLINE [
Emp, Date, Status, Sequence
A0, 2018-12-03, A, 0
A1, 2015-05-03, A, 0
B1, 2019-10-20, A, 0
B1, 2019-10-20, I, 1
B1, 2019-10-21, I, 0
];

Main:
Load
Emp,
Date,
//Status,
Max(Sequence) as Sequence
resident Temp
group by Emp, Date;//, Status;

left join 
Load
Emp,
Date,
Sequence,
Status
Resident Temp;
drop table Temp;

[summerrain]

Yes,  that's the way, thank you!