cancel
Showing results for 
Search instead for 
Did you mean: 

Sort and Pick up latest date in BODS

Former Member
0 Kudos

Hi All,

My Input Table is as below.

Emp ID          Dept ID          Salary      Date

1                         1                    100     01/16/2015  

2                         1                    250     01/10/2015

1                         1                    400     01/25/2015

2                         1                    500     01/01/2015

I need to sort the data based on Emp ID, Dept ID so expected o/p should be

Emp ID          Dept ID          Salary          Date

1                         1                    100          01/16/2015

1                          1                    400          01/10/2015

2                         1                    250          01/10/2015

2                         1                        500     01/01/2015

    From the above table, For Each Emp ID, Dept ID, pick up the record with latest date. That means final o/p should be

Emp ID          Dept ID          Salary          Date

1                         1                    100          1/16/2015

2                         1                    250           1/10/2015

Could you please explain me how to achieve this.

Regards.

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member187605
Active Contributor
Former Member
0 Kudos

Hi,

Do I need to Group by Emp ID and Dept ID?.

Regards.

former_member187605
Active Contributor
0 Kudos

Yes, that's your group key

Former Member
0 Kudos

Hi,

Apart from Emp id, Dept ID, I have few more columns.

With out all those fields, Group by is not accepting.

Should we include all fields in Group By clause?.

Regards.

mageshwaran_subramanian
Active Contributor
0 Kudos

No.

Input Table -> query transform (select Emp ID ,Dept ID  and Max(Date) group by Emp ID ,Dept ID) then join this result back to the input table with Emp ID ,Dept ID and date to get the other fields.

Former Member
0 Kudos

Hi..

My Job is running for long time. Is it because of Group by and Order By functions?.

Regards.

mageshwaran_subramanian
Active Contributor
0 Kudos

How many records in source table? . Check the joins

former_member211387
Contributor
0 Kudos

Add another column in the sorted dataset and store the value gen_row_num_by_group(Emp_ID, Date).

In the final output filter out the records where the groupby column = 1.

kind regards

Raghu

mageshwaran_subramanian
Active Contributor
0 Kudos

Input Table -> query transform (select Emp ID ,Dept ID  and Max(Date) group by Emp ID ,Dept ID) then join this result back to the input table with Emp ID ,Dept ID and date to get the salary

Former Member
0 Kudos

Hi Magesh,

My Query is still throwing error that Date should be in Group By list..

But I have put that Date in Max function in Mapping Tab.

Regards.