Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
summerrain
Contributor III
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
Specialist II
Specialist II

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
Specialist II
Specialist II

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

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

zhadrakas
Specialist II
Specialist II

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

Yes,  that's the way, thank you!