Logic Help

bicap · Post by **bicap** » Wed Dec 14, 2011 11:50 pm

Hello
I am new to datastage development would appriciate any help in logic for belo scenario

My input is seq file with data
oldID newID
1,2
2,3,
4,5
3,6

output expected is

1,6
2,6
4,5
3,6

Please let me know if more information needed

Thanks in advance

kamtammystuff · Post by **kamtammystuff** » Wed Dec 14, 2011 11:58 pm

Try using routines.

jwiles · Post by **jwiles** » Thu Dec 15, 2011 12:00 am

As is often requested for this forum, please describe in words what the business rules are that process your provided (and accurate) example input data in order to produce your provided (and accurate) example output data.

Without that information, the volunteer posters on this forum are forced to guess why the first row comes in as "1,2" and goes out as "1,6", which is not a dependable method to provide accurate answers.

Regards,

bicap · Post by **bicap** » Thu Dec 15, 2011 12:14 am

Thanks for quick replies

I got your point regarding business rules I apologise for the same

Business rule is,

Id's are updated with newID for eg first we have ID 1 which is updated with newID as 2 ; which (newID 2) upon further updation changed to Id 3 so the Input for eg

oldId newID
1 ---> changed to 2 ------day 1
2---> 3 ------------------day 2
4 --->5 -------------- day2
what I expect is for each ID we need to have recent ID
for above eg expected reult is
oldID newID
1,3
2,3
4,5

I hope this will explain the problem
thanks

ray.wurlod · Post by **ray.wurlod** » Thu Dec 15, 2011 12:29 am

So you are seeking the complete list of transitions for any input value, through to the endpoint of that list. Does that sound correct?

bicap · Post by **bicap** » Thu Dec 15, 2011 12:37 am

ray.wurlod wrote:So you are seeking the complete list of transitions for any input value, through to the endpoint of that list. Does that sound correct?

yes thats the requirement!

Kirtikumar · Post by **Kirtikumar** » Thu Dec 15, 2011 3:19 am

Few years back we had a subset of this requirement and we ended up using the recursive stored procedure for that.

What we did is created a chain of linked records.

Step1: Actual data in a table called as "ChainRels" containing OldID, NewID

Loop:
Insert in the ChainRels outfrom query (select tab1.OldID, tab2.NewID from OrigTable Tab1, Tab2 where Tab1.NewID = Tab2.OldID)

Repeat the loop till the output from the query is zero.

Now you have a table contains each records all IDs. From that do aggregate as Max(NewID) group by OldID.

This can be done in DS as well with looping logic in Sequence. Run the loop in sequence till you have output from a join/lookup.

bicap · Post by **bicap** » Thu Dec 15, 2011 3:26 am

Thanks Kirtikumar will try and let you know

chulett · Post by **chulett** » Thu Dec 15, 2011 7:47 am

jwiles wrote:As is often requested for this forum...

Thank you James... saved me some typing.

pandeesh · Post by **pandeesh** » Thu Dec 15, 2011 12:16 pm

One of the complex requirement!! :D

ray.wurlod · Post by **ray.wurlod** » Thu Dec 15, 2011 2:48 pm

Chaining records is easily done with delimited strings in stage variables. I prefer to use server job or BASIC Transformer and work with dynamic arrays in this case, because there is a really handy function called Locate() - and another called Find() - that can be used to search for a value in a dynamic array.

bicap · Post by **bicap** » Mon Dec 19, 2011 1:11 am

Thanks a lot everyone for replies we resolved it through looping logic in sequence as mention above !!