topic Re: Loading data - Grouping/aggregation issue in QlikView

Loading data - Grouping/aggregation issue

summerrain — Sat, 16 Nov 2024 02:10:48 GMT

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;

Re: Loading data - Grouping/aggregation issue

zhadrakas — Mon, 21 Oct 2019 09:53:15 GMT

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.

Re: Loading data - Grouping/aggregation issue

summerrain — Mon, 21 Oct 2019 10:17:49 GMT

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

Re: Loading data - Grouping/aggregation issue

zhadrakas — Mon, 21 Oct 2019 10:41:02 GMT

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;

Re: Loading data - Grouping/aggregation issue

summerrain — Mon, 21 Oct 2019 10:49:28 GMT

Yes, that's the way, thank you!