Transform historical database

mhurtgen · Participant Joined: 28 Aug 2018 Posts: 2 Points: 21

Hello,

We have a table giving the transitions between different statuses

date1 status1 status2
date2 status2 status3
....

date(n) status(n) status(n+1)

We would like to change the table to the form

status1 date_start1 date_end1
status2 date_start2 date_end2
....
status(m) date_start(m) date_end(m)

Is there a stage in DataStage that can do this?

Thank you for your help

ray.wurlod · Posted: Tue Aug 28, 2018 6:43 pm

Welcome aboard.

Can you write out the rules that govern how the start date and end date values are to be calculated?

(The answer to your first question is almost certainly yes. I am trying to pre-empt your second question.)

mhurtgen · Participant Joined: 28 Aug 2018 Posts: 2 Points: 21

Hello Ray,

Thank you for your answer.

I should have formulated the question differently.

I have not specified the relation between date_start and date. Actually they are closely related:

The second status(status2) starts on the day after the transition from status 1 to status 2, which is date1+1: date_start2 = date1+1

for status m date_start(m)=date(m-1)+1

The end date for status2(date_end2) is the day of the transition from status2 to status3: date_end2= date2

for status m date_end(m)=date(m) day

So the original question should have been summarized as follows:

From

date1 status1 status2
date2 status2 status3
....

date(n) status(n) status(n+1)

We would like to change the table to the form

status1 date_start1 date1
status2 date1+1 date2

status(n) date(n-1)+1 date(n)

Thank you for your help.

Kind regards

ray.wurlod · Posted: Mon Sep 03, 2018 9:24 am

Use sorted data. Use stage variables to "remember" the previous row (there are many examples here). The DateFromDaysSince() function gives you the ability to calculate date(n)+1.