How to generate a calendar Year Using DataStage

[Mandar_Deshpande]

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

[roy]

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,

[Mandar_Deshpande]

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

[ArndW]

Mandar,

you have 1 input value, are you looking for 1 output value 5 years hence or are you looking for a string with each day between the start and end dates?

[ray.wurlod]

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.

Code: Select all

Oconv(Iconv(svMyDate, "DYMD") + 1), "D-YMD[4,2,2]")

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.

[Mandar_Deshpande]

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?

[talk2shaanc]

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)

Code: Select all

Transformer ------> SeqFile

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.

[Prashantoncyber]

How to make dynamic both ways ie start date as well as end date?

[talk2shaanc]

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.

[ray.wurlod]

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?

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.

[ram1899]

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

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)

Code: Select all

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.

[talk2shaanc]

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]

You got me on this and I agree I did a small mistake in my logic

Thanks for Enlighting me


Have good one

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.