DSXchange

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

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

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.

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

-

Thanks guys!

Mat