Subtract the value from Previous row value

[Titto]

Hi,

I need some thoughts and logic on the follwoing requirement.
I have a set of data by date , week, period and amount
each weeks amount is added to next week till the end of the week of a particular month. My requirement is to write the amounts per each week by subtracting the amount from previous week amount .
Here is the sample data

Code: Select all

CSTNBR,RPT_DT,MTH_ID,WK_ID,AMT
1111111,2006-01-01,1,1,100
1111111,2006-01-08,1,2,300
1111111,2006-01-15,1,3,550
1111111,2006-01-22,1,4,850
1111111,2006-01-29,1,5,1000

I want this to be in follwoing way

Code: Select all

CSTNBR,RPT_DT,MTH_ID,WK_ID,AMT
1111111,2006-01-01,1,1,100
1111111,2006-01-08,1,2,200
1111111,2006-01-15,1,3,250
1111111,2006-01-22,1,4,300
1111111,2006-01-29,1,5,150

any help is appreciated... !!!

[kcbland]

Use a stage variable to track the "key" values that identify a group, and another stage variable to hold the last value seen. Use the appropriate logic to manage the output rows.

[Titto]

Could you please guide little bit more
I created 2 stage variables and using the below logic, i am missing something !!!

Code: Select all

 
StageVariable1 = StgAmt
StageVariable2 = StgMTHID

If If Read.MTH_ID = StgMTHID Then (Read.AMT - StgAMT) Else Read.AMT 

[kwwilliams]

First make sure your data is sorted on your key before entering the transformer. Then have something like this:

Code: Select all

CurrentMonthRecord = MonthID
CurrentAmount  = Read.Amt
WeekAmt = (If PrevMonthRecord = CurrentMonthRecord Then CurrentAmountt - PrevAmount)
PrevMonthRecord = MonthID
PrevAmount = Read.Amt

They need to be in that order. Because stage variables process sequentially, the WeekAmt will calculate before the PrevMonthRecord and PrevAmount record are set.

[Titto]

Thanks It worked!!!

[kwwilliams]

Your welcome. Thanks for posting back that the solution worked. Many times I look for a solution here and never no if I am going down the right path because no one posted back whether the solution worked for them.

[kcshankar]

Hi Titto,
Here is an alternate method for your requirement using Built In Routine.

Code: Select all

I need some thoughts and logic on the follwoing requirement. 
I have a set of data by date , week, period and amount 
each weeks amount is added to next week till the end of the week of a particular month. My requirement is to write the amounts per each week by subtracting the amount from previous week amount .

CSTNBR,RPT_DT,MTH_ID,WK_ID,AMT 
1111111,2006-01-01,1,1,100 
1111111,2006-01-08,1,2,300 
1111111,2006-01-15,1,3,550 
1111111,2006-01-22,1,4,850 
1111111,2006-01-29,1,5,1000 

CSTNBR,RPT_DT,MTH_ID,WK_ID,AMT 
1111111,2006-01-01,1,1,100 
1111111,2006-01-08,1,2,200 
1111111,2006-01-15,1,3,250 
1111111,2006-01-22,1,4,300 
1111111,2006-01-29,1,5,150

It can be done using RowProcGetPreviousValue,Built in Routine.
This routine will return the previous value passed into this.

Declare stage variable
RowProcGetPreviousValue(in.AMT)-----StageVar1

In column derivation
in.AMT - StageVar1

Limitation of this routine is,this routine should not be used more than one place in a job.

In your case:

Input AMT
100 100
300 200
550 250
850 300
1000 150


regards
kcs

[smitageorge]

Hi all,

How about if we need to compare the previous value with the present value.And have the result as:
If equal it should be same or else increment by 1.

Thanks
Smita

[kcshankar]

Hi Smita,
If iam right,If your requirement is like this....

source:
CSTNBR,RPT_DT,MTH_ID,WK_ID,AMT
1111111,2006-01-01,1,1,100
1111111,2006-01-08,1,2,100
1111111,2006-01-15,1,3,550
1111111,2006-01-22,1,4,550
1111111,2006-01-29,1,5,1000

Target:
CSTNBR,RPT_DT,MTH_ID,WK_ID,AMT,new_val
1111111,2006-01-01,1,1,100,100
1111111,2006-01-08,1,2,100 ,101
1111111,2006-01-15,1,3,550 ,550
1111111,2006-01-22,1,4,550 ,551
1111111,2006-01-29,1,5,1000,1000

then
Use RowProcCompareWithPreviousValue routine.
Declare it in Stagevariable
This routine compares with the previous value and gives output.
1 is returned if it is equal, otherwise 0 is returned.

Derivation for your new column will be,
If RowProcCompareWithPreviousValue (input.AMT) = 1 then input.AMT else input.AMT +1.
Hope it works fine

regards
kcs

[smitageorge]

Hi KC,

I think i haven't given the correct information.

I am having 2 colums in input

ID,DEL
5,7
5,2
7,3
7,4
7,5
8,2........

In the Output I need something like this

New,ID,DEL
1,5,7
1,5,2
2,7,3
2,7,4
2,7,5
3,8,2 .........

whenever the ID value is changing I should have an increment of 1 in New column.

I tried the to compare it with previous value but all i am getting is 1.

Thanks
Smita

[Sunshine2323]

Hi Smita,

This can easily be done using RowProcCompareWithPreviousValue as already suggested.

Declare 2 StageVariables:
StgCompare=RowProcCompareWithPreviousValue(Source.ID)
StgNew=if StgCompare=1 then StgNew else StgNew+1

Transformer Derivation for the Column New:
New=StgNew

Code: Select all

Output:

1,5,2 
1,5,7 
2,7,3 
2,7,4 
2,7,5 
3,8,2

Hope this helps:)

[kayarsenal]

Thank Sunshine, it worked. YOu make everything look easy. Hopefully I will get to ur level soon )