Finding most recent effectitive salary date
Moderators: chulett, rschirm, roy
Finding most recent effectitive salary date
Hi,
I need to findout most recent effective date.
Emp No Date Salary
100007 ^1999-11-01^ 000000000002152.57
100007 ^2000-11-01^ 000000000002152.57
100007 ^2001-01-01^ 000000000002152.57
100007 ^2001-11-01^ 000000000002152.57
100007 ^2002-11-01^ 000000000002152.57
100007 ^2003-04-01^ 000000000002152.57
100007 ^2003-09-01^ 000000000002152.57
100007 ^2003-11-01^ 000000000002152.57
10004 ^1998-04-01^ 000000000012500.00
10004 ^1999-04-01^ 000000000012500.00
10004 ^2000-04-01^ 000000000012500.00
10004 ^2000-07-01^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2001-02-06^ 000000000013350.00
10004 ^2001-02-28^ 000000000013350.00
10004 ^2001-04-01^ 000000000013350.00
10004 ^2001-08-31^ 000000000013350.00
10004 ^2002-01-01^ 000000000013350.00
Thanks
I need to findout most recent effective date.
Emp No Date Salary
100007 ^1999-11-01^ 000000000002152.57
100007 ^2000-11-01^ 000000000002152.57
100007 ^2001-01-01^ 000000000002152.57
100007 ^2001-11-01^ 000000000002152.57
100007 ^2002-11-01^ 000000000002152.57
100007 ^2003-04-01^ 000000000002152.57
100007 ^2003-09-01^ 000000000002152.57
100007 ^2003-11-01^ 000000000002152.57
10004 ^1998-04-01^ 000000000012500.00
10004 ^1999-04-01^ 000000000012500.00
10004 ^2000-04-01^ 000000000012500.00
10004 ^2000-07-01^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2001-02-06^ 000000000013350.00
10004 ^2001-02-28^ 000000000013350.00
10004 ^2001-04-01^ 000000000013350.00
10004 ^2001-08-31^ 000000000013350.00
10004 ^2002-01-01^ 000000000013350.00
Thanks
-
- Charter Member
- Posts: 64
- Joined: Sat Sep 17, 2005 10:42 am
I need to findout most recent salary effective date.
For based on Emp no,Effective Date,Salary.
Emp no Eff Date Salary
100007 ^ 1999-11-01 ^ 000000000002152.57
100007 ^2000-11-01^ 000000000002152.57
100007 ^2001-01-01^ 000000000002152.57
100007 ^2001-11-01^ 000000000002152.57
100007 ^2002-11-01^ 000000000002152.57
100007 ^2003-04-01^ 000000000002152.57
100007 ^2003-09-01^ 000000000002152.57
100007 ^2003-11-01^ 000000000002152.57
result:1999-11-01
10004 ^1998-04-01^ 000000000012500.00
10004 ^1999-04-01^ 000000000012500.00
10004 ^2000-04-01^ 000000000012500.00
10004 ^ 2000-07-01 ^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2001-02-06^ 000000000013350.00
10004 ^2001-02-28^ 000000000013350.00
10004 ^2001-04-01^ 000000000013350.00
10004 ^2001-08-31^ 000000000013350.00
10004 ^2002-01-01^ 000000000013350.00
result :2000-07-01
For based on Emp no,Effective Date,Salary.
Emp no Eff Date Salary
100007 ^ 1999-11-01 ^ 000000000002152.57
100007 ^2000-11-01^ 000000000002152.57
100007 ^2001-01-01^ 000000000002152.57
100007 ^2001-11-01^ 000000000002152.57
100007 ^2002-11-01^ 000000000002152.57
100007 ^2003-04-01^ 000000000002152.57
100007 ^2003-09-01^ 000000000002152.57
100007 ^2003-11-01^ 000000000002152.57
result:1999-11-01
10004 ^1998-04-01^ 000000000012500.00
10004 ^1999-04-01^ 000000000012500.00
10004 ^2000-04-01^ 000000000012500.00
10004 ^ 2000-07-01 ^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2001-02-06^ 000000000013350.00
10004 ^2001-02-28^ 000000000013350.00
10004 ^2001-04-01^ 000000000013350.00
10004 ^2001-08-31^ 000000000013350.00
10004 ^2002-01-01^ 000000000013350.00
result :2000-07-01
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Looks like it's the earliest effective date of the most recent salary. This makes it tricky as a persons salary may have gone up and down. Eg.
In this case no combination of aggregation, sort and grouping will give you the right answer. You will probably get the right salary (21000) but the wrong date (1999-01-01).
I think you should sort on EMP and DATE and send it to a transformer. Use Stage Variables to compare current row to previous row. Output the first row, read the second row, if EMP ID and SALARY is the same ignore the row and move to the next row, otherwise output the row. This will give you a unique start date and salary for every employee.
To find the most recent salaries do a remove duplicates on EMP taking the last record (most recent salary).
Code: Select all
10001 1999-01-01 000210000
10001 2001-01-01 000240000
10001 2003-01-01 000210000
I think you should sort on EMP and DATE and send it to a transformer. Use Stage Variables to compare current row to previous row. Output the first row, read the second row, if EMP ID and SALARY is the same ignore the row and move to the next row, otherwise output the row. This will give you a unique start date and salary for every employee.
To find the most recent salaries do a remove duplicates on EMP taking the last record (most recent salary).
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn