Filter out certain records

[former_member707806]

The PA0002 table contains multiple employee records with the same employee number. I want to extract only the employee record with the MAX end date using SAP Data Services. Where/how in the data flow can I do that?

[former_member707806]

Hi Werner,

It was in an ABAP dataflow.

Thanks

Jeff

[former_member707806]

Hi Werner,

Sorry it's taken so long to get back with you on this. Hope all is well with you and your loved ones. It's been a crazy year.

Turns out gen_row_num_per_group() is not even included in the built-in functions in my DS designer. How can I get that (and all built-in functions) loaded into DS? I will tell you the BASIS team had been messing around with the environments the last few months and may have inadvertently done something to limit the built-ins

Jeff

[werner_daehn]

No problem, I answer these questions just to stay ahead of things.

Regarding gen_row_num_per_group(), we are talking about a dataflow, not an ABAP dataflow, aren't we? Because in the latter most functions have not been implemented as ABAP version. These functions are available only in regular dataflow.

If it is not available in such regular dataflow, then your repository is not in order. The AL_FUNC and AL_FUNC_INFO repo tables in particular. In the Designer install directory, there are some ATLs which are being imported during a repo create or upgrade. You can import those manually as well - with all the normal precautions like backups etc. A repo with missing things makes us nervous though.

[former_member707806]

Thanks Werner,

But arrrggghh, seems like there should be an easier way to do this.

Thanks again.

Jeff

[werner_daehn]

There are other options, yes. Use a lookup_ext() - which does not work with ABAP tables - or a self join where one path does group the data per employee_number and max(end_date) and uses that as the join condition.

None of these options are easier in my opinion but slower and more dangerous. If I recall it correctly, the PA0002 has a date, not a timestamp, and there can be two records with the same end_date for one employee_number. The logic I showed deals with that also.

[werner_daehn]

Reading all records sorted by employee_number and end_date descending.

Then add another column with the gen_row_num_per_group(employee_number) which starts with a one whenever an new employee_number is found.

Next query filter out all rows where this generated column is not 1.