date Calculate
Moderators: chulett, rschirm, roy
date Calculate
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.
can someone please help me with that, how I can do that in transformer.
Thanks in advance.
-
- Participant
- Posts: 152
- Joined: Mon Mar 07, 2011 6:16 am
Re: date Calculate
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.
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.