Compare the Salary with the previous date salary
Moderators: chulett, rschirm, roy
Compare the Salary with the previous date salary
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4000
1234 01-01-2007 4000
1234 01-02-2007 4000
For a praticular employee I need to find out like
Present salary has to be compared with previous salary.
If salary is same then I need the previous eff_Date i.,e 01-01-2007 & also compare the previous
eff_Date i.,e 01-12-2006
i.,e Till the salaries are same i need the backtrack date.
For the above situtaion like i need 4000/- for 01-12-2006 date.
If the Salary's(Means to say Present and Previous) are not same like below one
I need Salary as 5000 & date is 01-02-2007
(I don't need to compare here since Salarys are differnt)
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4000
1234 01-01-2007 4500
1234 01-02-2007 5000
1234 02-02-2001 3000
1234 01-12-2006 4000
1234 01-01-2007 4000
1234 01-02-2007 4000
For a praticular employee I need to find out like
Present salary has to be compared with previous salary.
If salary is same then I need the previous eff_Date i.,e 01-01-2007 & also compare the previous
eff_Date i.,e 01-12-2006
i.,e Till the salaries are same i need the backtrack date.
For the above situtaion like i need 4000/- for 01-12-2006 date.
If the Salary's(Means to say Present and Previous) are not same like below one
I need Salary as 5000 & date is 01-02-2007
(I don't need to compare here since Salarys are differnt)
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4000
1234 01-01-2007 4500
1234 01-02-2007 5000
Last edited by srini.dw on Fri Jun 08, 2007 8:10 am, edited 1 time in total.
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
Re: Compare the Salary with the previous date salary
Try using the inbuild routine RowProcCompareWithPreviousValue in the transformer. If the supplied value compares with the previous value, 1 is returned, otherwise 0 is returned.
SMB
I am a little confused. When you say you need the "backtrack date", do you mean the date that particular salary first came in effect?
If thats the case, pass your data through an aggregator, group by EmpId and Salary and get the min(Eff_Dt). This will bring you to different salaries for a particular id.
THen pass it through a sort stage, sort on salary and emp id, and detect the duplicates. Pass it via a filter stage and filter on KeyChange() = 1 which will give you the max salary for a particular id.
BTW, the RowProcCompareWithPreviousValue is a server function and will not work in a parallel job.
If thats the case, pass your data through an aggregator, group by EmpId and Salary and get the min(Eff_Dt). This will bring you to different salaries for a particular id.
THen pass it through a sort stage, sort on salary and emp id, and detect the duplicates. Pass it via a filter stage and filter on KeyChange() = 1 which will give you the max salary for a particular id.
BTW, the RowProcCompareWithPreviousValue is a server function and will not work in a parallel job.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
whatever you specified logic will work previous examples.
But for particular employee demotion also will happen.How to implement below condition also.
I need to Backtrack recent salary Effective date.
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4500
1234 01-01-2007 5000
1234 01-02-2007 4500
Thanks
But for particular employee demotion also will happen.How to implement below condition also.
I need to Backtrack recent salary Effective date.
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4500
1234 01-01-2007 5000
1234 01-02-2007 4500
Thanks
Last edited by srini.dw on Fri Jun 08, 2007 8:59 am, edited 1 time in total.
The way I am looking at it, you are, infact, getting the date when the salary first came in effect.srini.dw wrote: I need to Backtrack recent salary Effective date.
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4000
1234 01-01-2007 4000
1234 01-02-2007 4000
In Above sistuation recent salary Effectivity date as 01-12-2006.
Did you understand my method. Will my advised method not work for you?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Do you intend for this to be used in a BASIC Transformer stage?
Using a Sort stage with key change column should allow a downstream Transformer stage to use stage variables to detect when the emp changes and to compare with the previous row. This will allow you to achieve your goal. Make sure data are hash or modulus (if integer) partitioned on empid.
Using a Sort stage with key change column should allow a downstream Transformer stage to use stage variables to detect when the emp changes and to compare with the previous row. This will allow you to achieve your goal. Make sure data are hash or modulus (if integer) partitioned on empid.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
DSguru2B wrote:srini.dw wrote: I need to Backtrack recent salary Effective date.
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4000
1234 01-01-2007 4000
1234 01-02-2007 40 ...
You can use like this :
Example :1
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4000
1234 01-01-2007 4000
1234 01-02-2007 4000
Take 1234 emplid and get the maximum effdate recod
1234 01-02-2007 4000
from here get the Salary Monthly
4000
Take 1234 emplid and salary monthly 4000 and get minimum effdate
01-12-2006
here you get the effective date as 01-12-2006
Example :2
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 3500
1234 01-01-2007 5000
1234 01-02-2007 6000
1234 emplid emplid and get the maximum effdate recod
1234 01-02-2007 6000
from here get the Salary Monthly
6000
1234 emplid and salary monthly 4000 and get minimum effdate
01-02-2007
here you get the effective date as 01-02-2007
Hope this will help you.
whatever you specified logic will work previous examples.
But for particular employee demotion also will happen.How to implement below condition also.
I need to Backtrack recent salary Effective date.
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4500
1234 01-01-2007 5000
1234 01-02-2007 4500
What about above sistuation...
Thanks
But for particular employee demotion also will happen.How to implement below condition also.
I need to Backtrack recent salary Effective date.
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4500
1234 01-01-2007 5000
1234 01-02-2007 4500
What about above sistuation...
Thanks
In this case/example 01-01-2007 will be salary effective date, is not it ?srini.dw wrote:whatever you specified logic will work previous examples.
But for particular employee demotion also will happen.How to implement below condition also.
I need to Backtrack recent salary Effective date.
Emplid EffDate Salary Monthly
1234 02-02-2001 3000
1234 01-12-2006 4500
1234 01-01-2007 5000
1234 01-02-2007 4500
What about above sistuation...
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Precede the Transformer stage with a Sort stage, in which you generate a sort key change column. Data coming into the Transformer will be sorted, and you will have a flag to indicate when a new sort key begins.
Stage variables are evaluated top-down in their grid. This means, for example, that while the second stage variable derivation is being evaluated, the third, fourth and subsequent stage variables still contain their values from the previous row. The second stage variable can access the third, fourth, etc., so as to compare the current row with the previous row.
For this to work properly in a parallel environment, the data will also need to be partitioned on the sort key, and that partitioning preserved into the Transformer stage.
Stage variables are evaluated top-down in their grid. This means, for example, that while the second stage variable derivation is being evaluated, the third, fourth and subsequent stage variables still contain their values from the previous row. The second stage variable can access the third, fourth, etc., so as to compare the current row with the previous row.
For this to work properly in a parallel environment, the data will also need to be partitioned on the sort key, and that partitioning preserved into the Transformer stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.