DSXchange

Hi I want to develop a job,with the following req,
i/p 3 columns

id,date,price
1,05/01/2011,100
1,06/01/2011,200
1,07/01/2011,100
2,06/01/2011,1000
3,12/01/2011,1000
4,10/01/2011,100
4,11/01/2011,200
4,12/01/2011,150

assume my run date is 12/012011,
Now there are 3 cases I need to discuss,

case 1:
records with id=1 having dates as 05,06,07 and prices are 100,200,100 respectively,
since rundate is 12/01/2011,I need to generate the same ids those number of times in order to fill the date gap and price should be last available date price should be carried on
in this case

the output should be
id,date,price
1,05/01/2011,100
1,06/01/2011,200
1,07/01/2011,100
1,08/01/2011,100
1,09/01/2011,100
1,10/01/2011,100
1,11/01/2011,100
1,12/01/2011,100



case 2:
records with id=2 having dates as 06/01/2011 and prices are 1000.
since rundate is 12/01/2011,I need to generate the same ids in order to fill the gap date and price should be last available date price should be carried on
in this case

the output should be
id,date,price
2,06/01/2011,1000
2,07/01/2011,1000
2,08/01/2011,1000
2,09/01/2011,1000
2,10/01/2011,1000
2,11/01/2011,1000
2,12/01/2011,1000



case 3:
records with id=3 having dates as 12/01/2011 and prices are 1000.
since rundate is 12/01/2011,I need not generate the ids in order to fill the date gap
in this case also as it is

the output should be
id,date,price
3,12/01/2011,1000


case 4:
records with id=4 having dates as 10/01/2011,11/01/2011,12/01/2011.
since rundate is 12/01/2011,I have dates till 12/01/2011 so no need to no need to fill any gap
in this case also as it is

the output should be
id,date,price
4,10/01/2011,100
4,11/01/2011,200
4,12/01/2011,150


final output combining case1,case2,case3,case4 my output would be
1,05/01/2011,100
1,06/01/2011,200
1,07/01/2011,100
1,08/01/2011,100
1,09/01/2011,100
1,10/01/2011,100
1,11/01/2011,100
1,12/01/2011,100
2,06/01/2011,1000
2,07/01/2011,1000
2,08/01/2011,1000
2,09/01/2011,1000
2,10/01/2011,1000
2,11/01/2011,1000
2,12/01/2011,1000
3,12/01/2011,1000
4,10/01/2011,100
4,11/01/2011,200
4,12/01/2011,150

thanks in advance for the help

Separate streams (via Copy stage?) funnelled back together.

Hi Ray,
Thanks for the prompt reply,
if is seperate into streams,how the date gap will be filled,
for eg:id 1is having dates 5th,6th,and 7th but if my rundate is 12th, then few more recs with dates 8th,9th,10th,11th should be added,any help How this could be done??

thanks

All records are processed by all streams. Sorting and partitioning are done ahead of the Copy stage and preserved throughout.

Hi Ray,
can you pls site an example,bcos,to use a copy stage I have more than 50,000 ids, I cannot substream and later funnel each stream,Pls let me know if my understanding is wrong,
I will be more happy if you can elaborate the answer with a small example
step by step

thanks
sai123

Hi Ray,
I have only one record
id,effdate
1,07/01/2011

the output expecting is

id,effdate
1,07/01/2011
1,08/01/2011
1,09/01/2011
1,10/01/2011
1,11/01/2011
1,12/01/2011

ie till current date,
Is there any way in datastage,to obtain more recs from one rec

thanks in advance Ray,

I assumed you'd use a separate stream for each style of processing, given that their rules differ. To generate multiple records, create a lookup containing just a list of dates, and do a range lookup between the max date from the data and the current date.

Hi Ray,
I am using version 7x I am afraid,I dont have range look up in version 7
thanks sai123

Then use a server job, where you CAN do a range lookup.

Hi Ray, This job is a big job already developed in Parallel job,
and it is in production,Now I cannot change the design or choice of job,
I need to go with present job,
Can you help in steps

thanks
sai1

If it's already in production, and working, why are you asking the question? If you worked with me, the rule would be "nothing is ever changed in production" - a proper software development lifecycle is followed.