How to generate a calendar Year Using DataStage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 12
- Joined: Wed Mar 02, 2005 1:59 am
How to generate a calendar Year Using DataStage
hello all,
I would like to generate a calendar year.
My input would be a Date such as 2000-01-01
My date value is YYYY-MM-DD.
I would like to get the next 5 years from the input date.
For example :
input date: 2000-01-01
Output date should be: 2000-01-01
2000-01-02
...
...
....
2001-12-31
....
till
...
2005-12-31
Are there any built-in transform or routines that I can use to achieve these?
Would so much appreciate your advice for the solution.
Thanks and Regards
Mandar Deshpande
I would like to generate a calendar year.
My input would be a Date such as 2000-01-01
My date value is YYYY-MM-DD.
I would like to get the next 5 years from the input date.
For example :
input date: 2000-01-01
Output date should be: 2000-01-01
2000-01-02
...
...
....
2001-12-31
....
till
...
2005-12-31
Are there any built-in transform or routines that I can use to achieve these?
Would so much appreciate your advice for the solution.
Thanks and Regards
Mandar Deshpande
Hi,
one way would be to calculate the end date depending on the start date
and just run a job that gets the dates and builds the data.
this job will naturally start with a transformer generating the rows.
If you need the full process reply and ask for it (I'll give it more attention when I can).
or others can fill in the blanks
IHTH,
one way would be to calculate the end date depending on the start date
and just run a job that gets the dates and builds the data.
this job will naturally start with a transformer generating the rows.
If you need the full process reply and ask for it (I'll give it more attention when I can).
or others can fill in the blanks
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
-
- Participant
- Posts: 12
- Joined: Wed Mar 02, 2005 1:59 am
Hi,
I have one row of the data i.e a date value 2000-01-01 and i am supposed to generate the dates till 5 years so if my start date is 2000-01-01 then my last date for the 5 years would be 2005-12-31 How to achieve this using a job?
Roy can u give me some more details regarding this?
would appreciate Thanks for ur early response
Thanks
Mandar
I have one row of the data i.e a date value 2000-01-01 and i am supposed to generate the dates till 5 years so if my start date is 2000-01-01 then my last date for the 5 years would be 2005-12-31 How to achieve this using a job?
Roy can u give me some more details regarding this?
would appreciate Thanks for ur early response
Thanks
Mandar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The easiest job design is to use a Transformer stage to generate the dates. Seed a stage variable (for example called svMyDate) with your starting date (or the day prior) and increment this. Constrain the output to the stage variable having the value of your end date.
Another design would be to create a before-stage subroutine to generate the dates into a text file, and process them from there as source.
Code: Select all
Oconv(Iconv(svMyDate, "DYMD") + 1), "D-YMD[4,2,2]")
Another design would be to create a before-stage subroutine to generate the dates into a text file, and process them from there as source.
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: 12
- Joined: Wed Mar 02, 2005 1:59 am
Hi Ray
I have tried using the solution you mentioned and was not able to generate the dates from one input date i need to generate it using one input date and it should get generated in the first run that means when i run my datastage job the dates should get generated for all the five years
can u sugget me some solution for this?
I have tried using the solution you mentioned and was not able to generate the dates from one input date i need to generate it using one input date and it should get generated in the first run that means when i run my datastage job the dates should get generated for all the five years
can u sugget me some solution for this?
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
Its one time thing or daily based ? You want it to be dynamic ?
One of the way , when you have the start date and end date.
Well your design should be as(Its same as Ray suggested)
JobParameter:
svMyDate=01-01-2005
svMyEndDate=01-01-2010
Stagevariables:
StageVar =StageVar+1 (initial value set to 0)
StageVar1=Oconv((Iconv(svMyDate, "D-MDY[2,2,4]") + StageVar), "D-MDY[2,2,4]")
Seqfile has one Column "InDate"
InDate=StageVar1
Constraints to output link:
@OUTROWNUM > 0 and Iconv(svMyEndDate,"D-MDY[2,2,4]") >= Iconv(StageVar1,"D-MDY[2,2,4]")
This would definately work. Do let me know if you want it dynamic, i.e., you dont have a end date.
One of the way , when you have the start date and end date.
Well your design should be as(Its same as Ray suggested)
Code: Select all
Transformer ------> SeqFile
svMyDate=01-01-2005
svMyEndDate=01-01-2010
Stagevariables:
StageVar =StageVar+1 (initial value set to 0)
StageVar1=Oconv((Iconv(svMyDate, "D-MDY[2,2,4]") + StageVar), "D-MDY[2,2,4]")
Seqfile has one Column "InDate"
InDate=StageVar1
Constraints to output link:
@OUTROWNUM > 0 and Iconv(svMyEndDate,"D-MDY[2,2,4]") >= Iconv(StageVar1,"D-MDY[2,2,4]")
This would definately work. Do let me know if you want it dynamic, i.e., you dont have a end date.
Shantanu Choudhary
-
- Participant
- Posts: 108
- Joined: Wed Jul 28, 2004 7:15 am
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
You should have at least one either start point or end point.
Its very simple needs only good logical thinking, as its all about playing with Iconv , Oconv and some other simple functions.
Say you want calender, something like 2yrs, 3months and 5 days from a given day.
JobParameter:
svMyDate=01-01-2005
MM=No of months from now
DD=No of days from now
YY=No of years from now
Stagevariables:
StageVar =StageVar+1 (initial value set to 0)
StageVar1=Oconv((Iconv(svMyDate, "D-MDY[2,2,4]") + StageVar), "D-MDY[2,2,4]")
SvEndDate=Oconv(Iconv(svMyDate['-',1,1]+MM:'-':svMyDate['-',2,1]:'-':svMyDate['-',3,1]+YY,"D-MDY[2,2,4]")+DD,"D-MDY[2,2,4]")
Seqfile has one Column "InDate"
InDate=StageVar1
Constraints to output link:
@OUTROWNUM > 0 and Iconv(SvEndDate,"D-MDY[2,2,4]") >= Iconv(StageVar1,"D-MDY[2,2,4]")
Similarly you can code when you have end point or you want your start point to be your systemdate.
![Smile :)](./images/smilies/icon_smile.gif)
Its very simple needs only good logical thinking, as its all about playing with Iconv , Oconv and some other simple functions.
Say you want calender, something like 2yrs, 3months and 5 days from a given day.
JobParameter:
svMyDate=01-01-2005
MM=No of months from now
DD=No of days from now
YY=No of years from now
Stagevariables:
StageVar =StageVar+1 (initial value set to 0)
StageVar1=Oconv((Iconv(svMyDate, "D-MDY[2,2,4]") + StageVar), "D-MDY[2,2,4]")
SvEndDate=Oconv(Iconv(svMyDate['-',1,1]+MM:'-':svMyDate['-',2,1]:'-':svMyDate['-',3,1]+YY,"D-MDY[2,2,4]")+DD,"D-MDY[2,2,4]")
Seqfile has one Column "InDate"
InDate=StageVar1
Constraints to output link:
@OUTROWNUM > 0 and Iconv(SvEndDate,"D-MDY[2,2,4]") >= Iconv(StageVar1,"D-MDY[2,2,4]")
Similarly you can code when you have end point or you want your start point to be your systemdate.
Shantanu Choudhary
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Use a job parameter for start date rather than hard-coding it as in my example. Use a row count or an end date (job parameter?) to constrain the output.Mandar_Deshpande wrote:Hi Ray
I have tried using the solution you mentioned and was not able to generate the dates from one input date i need to generate it using one input date and it should get generated in the first run that means when i run my datastage job the dates should get generated for all the five years
can u sugget me some solution for this?
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.
Don't we need an input and output links when using the Trans
Hi Guys,
I'm bit confused seeing all your solutions for this.
When using a transformer stage we need and input link and output link. The no of rows sent through output link depends on the no of rows coming from input.
If you don't have the Input from transformer then you will not be able to send any data to the output seq file you mentioned
I have tried your solution by using the logic you mentioned it didn't work Job has run sucessfully but no data in output Seq file.
To do this we need an input and output from transformer to generate the dates
Correct me if I'm wrong
I will also try for this solution
Thank you
I'm bit confused seeing all your solutions for this.
When using a transformer stage we need and input link and output link. The no of rows sent through output link depends on the no of rows coming from input.
If you don't have the Input from transformer then you will not be able to send any data to the output seq file you mentioned
I have tried your solution by using the logic you mentioned it didn't work Job has run sucessfully but no data in output Seq file.
To do this we need an input and output from transformer to generate the dates
Correct me if I'm wrong
I will also try for this solution
Thank you
talk2shaanc wrote:Its one time thing or daily based ? You want it to be dynamic ?
One of the way , when you have the start date and end date.
Well your design should be as(Its same as Ray suggested)
JobParameter:Code: Select all
svMyDate=01-01-2005
svMyEndDate=01-01-2010
Stagevariables:
StageVar =StageVar+1 (initial value set to 0)
StageVar1=Oconv((Iconv(svMyDate, "D-MDY[2,2,4]") + StageVar), "D-MDY[2,2,4]")
Seqfile has one Column "InDate"
InDate=StageVar1
Constraints to output link:
@OUTROWNUM > 0 and Iconv(svMyEndDate,"D-MDY[2,2,4]") >= Iconv(StageVar1,"D-MDY[2,2,4]")
This would definately work. Do let me know if you want it dynamic, i.e., you dont have a end date.
ram1899
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
Ram,
You can just have one output link, without any input link.
Trick:
1. Column value should be hardcoded, or derived the way its done my example in this post.
2. Have at least one stage variable in the job(may or may not have any purpose, but required for successful compilation of job).
3. limit the Output rows to be written to file, by giving a constraints. something like @OUTROWNUM < 5, to get four rows in the file. If constraints not given the job will keep writing into the target, same value that you have hardcoded over and over again.
Its impossible that the steps i have given didnt work for you. As I have tested it as well as Mandar (person who posted this thread) has also confirmed the same personally.
You can just have one output link, without any input link.
Trick:
1. Column value should be hardcoded, or derived the way its done my example in this post.
2. Have at least one stage variable in the job(may or may not have any purpose, but required for successful compilation of job).
3. limit the Output rows to be written to file, by giving a constraints. something like @OUTROWNUM < 5, to get four rows in the file. If constraints not given the job will keep writing into the target, same value that you have hardcoded over and over again.
Its impossible that the steps i have given didnt work for you. As I have tested it as well as Mandar (person who posted this thread) has also confirmed the same personally.
Shantanu Choudhary
Shantanu -- You are correct
You got me on this and I agree I did a small mistake in my logic
Thanks for Enlighting me
Have good one
Thanks for Enlighting me
Have good one
talk2shaanc wrote:Ram,
You can just have one output link, without any input link.
Trick:
1. Column value should be hardcoded, or derived the way its done my example in this post.
2. Have at least one stage variable in the job(may or may not have any purpose, but required for successful compilation of job).
3. limit the Output rows to be written to file, by giving a constraints. something like @OUTROWNUM < 5, to get four rows in the file. If constraints not given the job will keep writing into the target, same value that you have hardcoded over and over again.
Its impossible that the steps i have given didnt work for you. As I have tested it as well as Mandar (person who posted this thread) has also confirmed the same personally.
ram1899