datastage job logic

sai123 · Post by **sai123** » Wed Jan 12, 2011 12:33 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Jan 12, 2011 4:39 pm

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

sai123 · Post by **sai123** » Wed Jan 12, 2011 4:47 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Jan 12, 2011 7:17 pm

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

sai123 · Post by **sai123** » Wed Jan 12, 2011 7:32 pm

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

sai123 · Post by **sai123** » Wed Jan 12, 2011 7:35 pm

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,

ray.wurlod · Post by **ray.wurlod** » Wed Jan 12, 2011 10:31 pm

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.

sai123 · Post by **sai123** » Thu Jan 13, 2011 4:06 am

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

ray.wurlod · Post by **ray.wurlod** » Thu Jan 13, 2011 4:46 am

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

sai123 · Post by **sai123** » Thu Jan 13, 2011 7:25 am

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

ray.wurlod · Post by **ray.wurlod** » Thu Jan 13, 2011 2:15 pm

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.