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
Transform historical database
Moderators: chulett, rschirm, roy
Transform historical database
M. Hurtgen
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.)
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
date rules
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.