Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

summerrain
New Contributor III

Loading data - Grouping/aggregation issue

Hi,

 

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

I would like to load from this table

EmpDateStatusSequence
A02018-12-03A0
A12015-05-03A0
B12019-10-20A0
B12019-10-20I1
B12019-10-21I0

 

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

EmpDateStatus
A02018-12-03A
A12015-05-03A
B12019-10-20I
B12019-10-21I

 

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;

 

Labels (4)
1 Solution

Accepted Solutions
zhadrakas
Valued Contributor

Re: Loading data - Grouping/aggregation issue

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;

View solution in original post

4 Replies
zhadrakas
Valued Contributor

Re: Loading data - Grouping/aggregation issue

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
New Contributor III

Re: Loading data - Grouping/aggregation issue

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

zhadrakas
Valued Contributor

Re: Loading data - Grouping/aggregation issue

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;

View solution in original post

summerrain
New Contributor III

Re: Loading data - Grouping/aggregation issue

Yes,  that's the way, thank you!