YTD BASED ON THE MONTH NUMBER
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
YTD BASED ON THE MONTH NUMBER
Hello,
My source is Oracle,
And i have this columns in my source
FSCL yr,MTH_NUM,AMT
And my requirement is to calculate the YTD based on the month.
So for example if i am in the month num 2, then the YTD should be AMT in Month1 +AMT in month2
If i am in Month 3 then the YTD should be AMT in Month1 +AMT in Month2 +AMT in Month3 and so on,
My source is Oracle,
And i have this columns in my source
FSCL yr,MTH_NUM,AMT
And my requirement is to calculate the YTD based on the month.
So for example if i am in the month num 2, then the YTD should be AMT in Month1 +AMT in month2
If i am in Month 3 then the YTD should be AMT in Month1 +AMT in Month2 +AMT in Month3 and so on,
anudpETL
This can be achieved by use of transformer stage variables:
Note: In the job, either run Sort and Transformer stages in Sequential mode or hash by FsclYr.
1. Sort incoming data by FsclYr, MthNum.
2. In transformer
2.1. Create following stage variables
Note: In the job, either run Sort and Transformer stages in Sequential mode or hash by FsclYr.
1. Sort incoming data by FsclYr, MthNum.
2. In transformer
2.1. Create following stage variables
- IF in.FsclYr <> svFsclYr THEN In.Amt ELSE (svYTDAmt + In.Amt) ===> svYTDAmt
in.FsclYr ===> svFsclYr
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
A Small correction to my Question
With this Data there is another additional field in the input i.e source_lin_ID,So the data is like this
Src_lin_id,FSCL_YR,MTH_NUM,AMT
1,1997,1,20
1,1997,2,30
.
.
.
2,1997,1,30
2,1997,2,40
.
.
.
.
3,1997,1,40
3,1997,2,50
.
.
.
.
So the key should be for the SRC_Lin_Id and year to calculate the YTD using month.
[My source is Oracle,
A.nd i have this columns in my source
FSCL yr,MTH_NUM,AMT
And my requirement is to calculate the YTD based on the month.
So for example if i am in the month num 2, then the YTD should be AMT in Month1 +AMT in month2
If i am in Month 3 then the YTD should be AMT in Month1 +AMT in Month2 +AMT in Month3 and so on.
With this Data there is another additional field in the input i.e source_lin_ID,So the data is like this
Src_lin_id,FSCL_YR,MTH_NUM,AMT
1,1997,1,20
1,1997,2,30
.
.
.
2,1997,1,30
2,1997,2,40
.
.
.
.
3,1997,1,40
3,1997,2,50
.
.
.
.
So the key should be for the SRC_Lin_Id and year to calculate the YTD using month.
[My source is Oracle,
A.nd i have this columns in my source
FSCL yr,MTH_NUM,AMT
And my requirement is to calculate the YTD based on the month.
So for example if i am in the month num 2, then the YTD should be AMT in Month1 +AMT in month2
If i am in Month 3 then the YTD should be AMT in Month1 +AMT in Month2 +AMT in Month3 and so on.
anudpETL
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
Hmmm... okay, if you say so. I found what seemed to be a number of valid answers, even if some of them were for SQL Server. At its core, SQL is SQL in spite of Oracle's sometimes unique take on the subject, so they can apply to Oracle as much as any other SQL-based solution.
You might want to explain what "stage variable doesn't work" means here. To me it looks like pretty standard group change detection logic and that can be done via stage variables rather easily. Now that previous answer may need to be fleshed out a bit to include all of the relevant columns but it can certainly be made to work for that purpose.
You might want to explain what "stage variable doesn't work" means here. To me it looks like pretty standard group change detection logic and that can be done via stage variables rather easily. Now that previous answer may need to be fleshed out a bit to include all of the relevant columns but it can certainly be made to work for that purpose.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers