New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
New Contributor III

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:
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:

Emp,
Date,
Status,
Max(Sequence)

resident Temp

group by Emp, Date, Status;

drop table Temp;

Labels (4)

• QlikView

1 Solution

Accepted Solutions
Valued Contributor II

try that:

``````Temp:
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:
Emp,
Date,
//Status,
Max(Sequence) as Sequence
resident Temp
group by Emp, Date;//, Status;

left join
Emp,
Date,
Sequence,
Status
Resident Temp;
drop table Temp;``````
4 Replies
Valued Contributor 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.

New Contributor III

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

Valued Contributor II

try that:

``````Temp:
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:
Emp,
Date,
//Status,
Max(Sequence) as Sequence
resident Temp
group by Emp, Date;//, Status;

left join