Compare the Salary with the previous date salary

[srini.dw]

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

[baglasumit21]

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.

[DSguru2B]

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.

[srini.dw]

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

[DSguru2B]

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.

The way I am looking at it, you are, infact, getting the date when the salary first came in effect.

Did you understand my method. Will my advised method not work for you?

[ray.wurlod]

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.

[Abburi]

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.

[srini.dw]

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

[srini.dw]

Hi,Ray
I understand i have to use Transformer Stage and also i have to create stage variables to keep checking present and prevoius salaty's.Before Transformer i have to do Hash partioning baesd on Keys columns.

Can you Please go through the in depth...How to do

If i have above columns.


Thanks

[Abburi]

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

In this case/example 01-01-2007 will be salary effective date, is not it ?

[srini.dw]

Yes...

1234 01-02-2007 4500

[ray.wurlod]

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.