split one row in more row

[LucaG]

Hello all,
my first post here.
I'm a newbie in datastage; i made some courses sometimes ago at the IBM location in italy.
The problem is the following:
i have one row that contain two date like this:
Data_i= 2013-01-15 Data_f= 2013-03-20
I need to create a new row for every month and calculate the number of the day in that month.
I made an example with the Data that I write early:

1) 15-01-2013 31-01-2013 17( number of the day in that month (31-15+1))
2) 01-02-2013 28-02-2013 28 (because February is not present in the input data and it's in the interval i get the number of the day in that month)
3) 01-03-2013 20-03-2013 20 (the number of the day of the last data)

I hope you understand the problem.
I try to explain it best as i can.
I would like to say thank you because in the past (recent past because i'm 21 :D) I found some great solution in that forum.

Greetings all and thanks in advance sorry for bad English.

Luca G.

[pavi]

What do you mean by create each row for each month?you mentioned two different dates so if the difference is 3 months do you want to create 3 records?Can you please let us know the actual problem you are facing with proper expectations of your requirement?

[LucaG]

exactly ! for every month i need a record and then i need to count the number of the day in that month like in this example :

if i suppose to have that situation: Data_i= 2013-01-15 Data_f= 2013-03-20

i need this output :

1) 15-01-2013 31-01-2013 17( number of the day in that month (31-15+1))
2) 01-02-2013 28-02-2013 28 (because febrary is not present in the input data and it's in the interval i get the number of the day in that month)
3) 01-03-2013 20-03-2013 20 (the number of the day of the last data)

where 1-2-3 are the new record created

[pavi]

You need to use transformer functions in order to meet your expectations.

Data_i= 2013-01-15 Data_f= 2013-03-20

Last day of month function to get the last day for 2013-01-15.Put it in a stage variable and convert the last day and Data_i into Julian days and get the difference. It gives you the days in between.Use the stage variable above and get the difference of months between the stage var and Data_f and if the difference is greater than 1 repeat the same process above.Even you can use loop logic in order to do it.

[chulett]

Luca - what 8.x version are you running? Asking because it would need to be at least 8.5 for 'transformer looping' to be an option for you.

[LucaG]

i'm using the 8.1 version so no loop in transform available. Thanks for the interesting about the problem. Thanks you too paavi.

[LucaG]

My boss tell me that in the company datawarehouse exits a table "L2D_Tempo" that contain for every day from 1950 to 2099 the column day, month , year and data like tihs one:

01 01 1950 01/01/1950
02 01 1950 01/02/1950
......
31 12 2099 31/12/2099

So if put in join my source record(data_i 15/01/2013 data_f 20/03/2013) with this table using a look up stage and setting up the range like this:

data(L2D_Tempo table) >= data_i AND
data(L2D_Tempo table) <= data_f

i should obtain one row for every day between data_i and data_f.

15 01 2013 15/01/2013
16 01 2013 16/01/2013
....
31 01 2013 31/01/2013
...
01 02 2013 01/02/2013
02 02 2013 02/02/2013
....
28 02 2013 28/02/2013
....
01 03 2013 01/03/2013
...
20 03 2013 20/03/2013

next step should be the aggregator stage.
For obtain my output target i group for the column month,year and i calculate the min data, the max data and count the number of the day.

I think this solve my problem.
I miss something?

Thanks in advance

Luca G.