Query with filter and grouping?

[Former Member]

Hello!

An InfoProvider contains, say, the following data:

Object	Version	Group	Value
1001	1	A	10
1001	2	C	20
1001	3	B	15
1002	1	B	100
1002	3	A	200
1002	5	C	600
1003	1	B	50
1003	4	C	40
1003	5	C	60
1003	6	B	70
1004	5	C	900

I am interested in only one record per "Object", namely that with the highest version.

They are those:

Object	Version	Group	Value
1001	3	B	15
1002	5	C	600
1003	6	B	70
1004	5	C	900

Furthermore, the result should be less detailed. I don't need the Objects in the result, but only one result for every "Group".

The final result should look like this:

Group	Sum of Value
B	85
C	1500

Can this be done with a query?

Thanks, regards,

Alfred

[Former Member]

Hello Alfred,

This can not be done in query designer - as long as you have Group in rows all three records for object 1001 are taken into account.

This has to be done in transformation e.g. using http://help.sap.com/abapdocu_731/en/abapdelete_duplicates.htm.

BR

Ondrej

[Former Member]

Dear Ondrej,

it is possible to obtain the list with highest version only.

(Create a Formula Variable on "Version", use it in a formula. Then create a condition with TOP N, N = 1 on that formula.)

But to obtain this via condition, it is necessary, that "Object" and "Version" are the leftmost characteristics.

And now I am not sure, if it is possible, based on this result, to do further aggregation along "Group".

Thanks and best regards,

Alfred

[Former Member]

Well conditions are evaluated after aggregation, so you can get last version, but you can not aggregate any further (with query designer).

The only other option would be exception aggregation, where you can get last value for version, but when you use group in rows, it gets last version value of object for each group.

BR

Ondrej