Transform historical database

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
mhurtgen
Participant
Posts: 2
Joined: Tue Aug 28, 2018 2:07 am

Transform historical database

Post by mhurtgen »

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
M. Hurtgen
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
mhurtgen
Participant
Posts: 2
Joined: Tue Aug 28, 2018 2:07 am

date rules

Post by mhurtgen »

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
M. Hurtgen
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply