crazy requirement in DataStage

[dssiddu]

Hi All

Here is the crazy requirement, just let me know your inputs
Input Data:
111, 100, 200,MDC
222, 200, 300, AAA--->
333, 300, 400,ABC
444, 400, 500,BCD

Ex: record BCD got changed to DCB then i have to get the 222 from navigating 500 -> 400-> 300 -> 200 ->222

Note: No SQL, everything have to do in Data stage only and records always doesn't looks like this and not always bottom to top or top to bottom everything random only .

Thanks a lot in Adv

[ray.wurlod]

Not enough information.

What is the expected output?

Please provide a written specification of the transformation needed in the requirement.

Why is this required? It it, perhaps, an interview question?

[dssiddu]

Thanks Ray

Input Data:
111, 100, 200,MDC
222, 200, 300, AAA--->
333, 300, 400,ABC
444, 400, 500,BCD

Ex: record BCD got changed to DCB then i have to get the 222 from navigating 500 -> 400-> 300 -> 200 ->222

Output Data Should be:(only changed record)

444, 400, 500,DCB,222

[g_rkrish]

As ray said no enough info,is your i/p is comma delimited file with four columns?

[SURA]

cant understand what you are trying to achive / what the need is.

DS User

[dssiddu]

hello all sorry

My Input Data in table only with 4 fields as below :
ID, ParentID,ChildID,NAME
111, 100, 200,MDC
222, 200, 300, AAA--->
333, 300, 400,ABC
444, 400, 500,BCD

Basically AAA is one special value and need to get always AAA id (it may differ for other set of records).
Ex: If record BCD got changed to DCB then 500 is the child value , so directly i can't go fetch 222 by removing other names because no idea 222 is child of 500 which changed now. So
i have to go first changed record here 500 , for 500 parent is 400 (which is not AAA) then for 400 parent is 300(which is not AAA) then for 300 parent is 200 which have AAA value so get the id ie 222.

Finally output should be 444, 400, 500,DCB,222

Hope its very clear now

[PhilHibbs]

Nope. There is no record with an ID of 500. The record with an ID of 444 has a ParentID of 400 and a ChildID of 500, but no record has either of those as its ID.

[sanjayS]

I didnt get what it means 'When record BCD got changed to DCB '

Could you plz explain..

Thanks,
Sanjay.

[jwiles]

Questions that need answers:

1) How do you know when the NAME column has been changed? Your example: "When record BCD got changed to DCB". Your sample data doesn't indicate there was a changed NAME.

2) You state that the data rows can arrive to the job in any order. Does this also mean that rows from different hierarchies can be intermingled? Is there any overlap of ID values across hierarchies that might make it more difficult to reorder the data to group hierarchies together?

Regards,