Page 1 of 1
date Calculate
Posted: Wed Mar 23, 2011 9:56 am
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.
Re: date Calculate
Posted: Wed Mar 23, 2011 11:07 am
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.
Posted: Wed Mar 23, 2011 11:17 am
by chulett
Server job.
Posted: Wed Mar 23, 2011 12:18 pm
by sush26283
chulett wrote:Server job.
yes server job
Posted: Wed Mar 23, 2011 12:42 pm
by chulett
I know, it wasn't a question... I posted it because the answer you got was specific to PX jobs.
Posted: Wed Mar 23, 2011 12:48 pm
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
Posted: Wed Mar 23, 2011 1:33 pm
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.
Posted: Wed Mar 23, 2011 1:38 pm
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..
Posted: Wed Mar 23, 2011 1:53 pm
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)