DSXchange

Posted: **Thu Jan 12, 2006 12:37 pm**

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... !!!

Posted: **Thu Jan 12, 2006 1:49 pm**

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.

Posted: **Thu Jan 12, 2006 2:28 pm**

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

Posted: **Thu Jan 12, 2006 2:37 pm**

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.

Posted: **Thu Jan 12, 2006 2:48 pm**

Thanks It worked!!!

Posted: **Thu Jan 12, 2006 3:30 pm**

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.

Posted: **Wed Jan 18, 2006 9:16 am**

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

Posted: **Thu Jan 19, 2006 1:37 pm**

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

Posted: **Thu Jan 19, 2006 2:22 pm**

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

Posted: **Thu Jan 19, 2006 4:28 pm**

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

Posted: **Fri Jan 20, 2006 5:44 am**

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:)

Posted: **Sun Jan 22, 2006 9:18 am**

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

)

DSXchange

Subtract the value from Previous row value

Subtract the value from Previous row value