Populate values of next record in previous record

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
manojbh31
Premium Member
Premium Member
Posts: 83
Joined: Thu Jun 21, 2007 6:41 am

Populate values of next record in previous record

Post by manojbh31 »

Hello all,

I have 5 records as below

Code: Select all

Id      Date           End date
1234    2017-08-09     2017-08-10
1234    2017-08-10     2017-08-11
1234    2017-08-11     2017-08-12
1234    2017-08-12     2017-08-13
1234    2017-08-13     2099-12-31
I need to pick next record date value and populate in end date of old record as above, please help on how to implement this .
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Go in reverse order.
-craig

"You can never have too many knives" -- Logan Nine Fingers
manojbh31
Premium Member
Premium Member
Posts: 83
Joined: Thu Jun 21, 2007 6:41 am

Post by manojbh31 »

Craig,

I am not getting you, please explain.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You can't do anything with the "previous" records unless we're talking a database as the target. I'm assuming we're talking about files here so the suggestion is to sort the data descending... i.e. process them in the opposite order posted so that the date in the current record can be used as the date in the next record rather than the previous one.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Timato
Participant
Posts: 24
Joined: Tue Sep 30, 2014 10:51 pm

Post by Timato »

On the offchance you are sourcing from a database i'd suggest reading up on 'window' functions for your rdbms. Perfect for reconstructing SCDs (i assume...)
Post Reply