YTD BASED ON THE MONTH NUMBER

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
anudeepdasari001
Participant
Posts: 58
Joined: Mon Dec 14, 2015 3:16 pm
Location: Arizona

YTD BASED ON THE MONTH NUMBER

Post by anudeepdasari001 »

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,
anudpETL
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Post by rkashyap »

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
  • IF in.FsclYr <> svFsclYr THEN In.Amt ELSE (svYTDAmt + In.Amt) ===> svYTDAmt
    in.FsclYr ===> svFsclYr
2.2. Write incoming columns and svYTDAmt to output.
anudeepdasari001
Participant
Posts: 58
Joined: Mon Dec 14, 2015 3:16 pm
Location: Arizona

Post by anudeepdasari001 »

Thanks,
I checked that Logic, it works Good . Will this work for all the years and for all the line items.
anudpETL
anudeepdasari001
Participant
Posts: 58
Joined: Mon Dec 14, 2015 3:16 pm
Location: Arizona

Post by anudeepdasari001 »

Is there any way whether that can be accomplished in Oracle Source stage itself.
anudpETL
anudeepdasari001
Participant
Posts: 58
Joined: Mon Dec 14, 2015 3:16 pm
Location: Arizona

Post by anudeepdasari001 »

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.
anudpETL
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I'd suggest you put something like "Oracle sql ytd" into your googler of choice and go through the various discussions online. You are not the first person to wonder how to do this in SQL. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
anudeepdasari001
Participant
Posts: 58
Joined: Mon Dec 14, 2015 3:16 pm
Location: Arizona

Post by anudeepdasari001 »

Stage Variable Doesn't work for Different SRC_LIN_ID,chulet i have searched enough regarding this topic, but no valid answer
anudpETL
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Post Reply