date Calculate

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sush26283
Participant
Posts: 68
Joined: Thu May 20, 2010 11:55 am

date Calculate

Post by sush26283 »

I need to calculate date, and the requirement is, if the date in column column_X is between the current date and the last 4 years then 'Y' else 'N'
can someone please help me with that, how I can do that in transformer.
Thanks in advance.
soumya5891
Participant
Posts: 152
Joined: Mon Mar 07, 2011 6:16 am

Re: date Calculate

Post by soumya5891 »

1. In a transformer or modifier stage define a stage variable say sVar1
seperate out the day and month part from the current date.Now concatinate it with current Year -4 on proper date format.This value is converted into date and stored into sVar1.
2. Now compare between sVar1 and current date with your date and do your processing.


Or

1. In a transfrimer define a stage var sVar1.Four years=365*4=1460(neglecting leap year)
sVar1=DateFromDaysSince(-1460,Current Date).
2. Now compare between sVar1 and current date with your date and do your processing.

Or

1. In a row generator stage generate a row and then in a transfromer stage calculate sVar1 like previous step.And make a dummy col with defaulted 1
2.Make a dummy column as set its value 1 in the incoming data.
3.join 1 and 2 on the basis of dummy col.Then do your processing.

I think in 3 approach the date calculation will be done once,so performence wise this will be better.
Soumya
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Server job.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sush26283
Participant
Posts: 68
Joined: Thu May 20, 2010 11:55 am

Post by sush26283 »

chulett wrote:Server job.
yes server job
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I know, it wasn't a question... I posted it because the answer you got was specific to PX jobs.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sush26283
Participant
Posts: 68
Joined: Thu May 20, 2010 11:55 am

Post by sush26283 »

chulett wrote:I know, it wasn't a question... I posted it because the answer you got was specific to PX jobs.
i did figure that out, so what would you suggest
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Use a database stage (eg ODBC stage) to service the reference link, and use user-defined SQL that specifies the BETWEEN condition. You can generate the two dates using SQL functions.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sush26283
Participant
Posts: 68
Joined: Thu May 20, 2010 11:55 am

Post by sush26283 »

ray.wurlod wrote:Use a database stage (eg ODBC stage) to service the reference link, and use user-defined SQL that specifies the BETWEEN condition. You can generate the two dates using SQL functions. ...
but wont it be better performance wise if we do it in transformer itself..
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Depends mainly on where the database is.

Within the transformer stage it's a simple If..Then..Else test with a binary condition (date >= fouryearsago And date <= currentdate)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply