DSXchange

I have to rearrange data from an activities table to have an activity and its consecutive activity in a single row from historical data which has each activity recorded (1 record for each activity)

I have data similar to below in the history table

id,app_id,task
1,1234,entry
2,3245,entry
3,1234,accepted
4,1234,closed

The expected output is

app_id,task,next_task

1234,entry,accepted
1234,accepted,closed
1234,closed,null
3245,entry,null

if there is a consecutive task, then show the task value in the column
next_task, if does not exist display as null

I am clueless how to achieve it

Please share your thoughts

In a transformer, stage variables are evaluated top to bottom, so system can cache value of "previous row", while processing the "current row" data, so you can use data from two rows at the same time.

There are numerous discussion threads on this topic. One example viewtopic.php?p=448233

Begin by sorting the data by app_id then by id.
Then use stage variables to detect change of app_id (new group) and to process adjacent pairs of rows.

Note that there is no "look ahead" in DataStage - but you can always sort in reverse order!