Splitting data by periods

[Mat01]

Hi All,

I have a requirement to split my incoming data in multiple rows by periods. I have to read 2 fields: begin_day and termination_day and split the incoming record into as many records as needed with each of them having a one month lengh. For example, if I have begin_day = 01/01/2004 and termination_day = 31/12/2004 on my record. I have to produce 12 records with the following day fields:

1- begin_day = 01/01/2004 and termination_day = 01/31/2004
2- begin_day = 02/01/2004 and termination_day = 02/29/2004
... And so on.

As anyone ever tried something similar? The jobs can be coded in Server or in PX.

Thanks

Mat

[rasi]

Hi

First get the min(Begin_Date) and Max(termination_date) from your source file which will be used as a parameter. Create a job which does the transformation and creates the target file. Use this job to run in the begin_date and termination_date loop. Use the parameter(begin_date,termination_date) append the target file for consecutive runs. In the job make sure that use select records based on the parameter.

All the best

Cheers
Siva

[anupam]

it seems that you want to split the data on the basis of month. If yes, then you can derive the month from the start_date and then based on the month can write a contraint which will redirect the data of different months to different month based sequential files.

[badri]

Hi,

Try this...

m1 ... m12 are the months. In the target add 12 columns additionally like below
m1....m12 r1...r12 set r1 = 1 r2 = 2...and so on.
Now use pivot stage to transform m1 ... m12 and r1...r12 in to rows like below

monthval rowval
m1 1
m2 2
m3 3
.
.
.
.
Hope this gives you an idea

-

[Mat01]

Thanks guys!

Mat