DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
mhurtgen
Participant



Joined: 28 Aug 2018
Posts: 2

Points: 21

Post Posted: Tue Aug 28, 2018 3:48 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54407
Location: Sydney, Australia
Points: 295092

Post Posted: Tue Aug 28, 2018 6:43 pm Reply with quote    Back to top    

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.)

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
mhurtgen
Participant



Joined: 28 Aug 2018
Posts: 2

Points: 21

Post Posted: Wed Aug 29, 2018 1:19 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54407
Location: Sydney, Australia
Points: 295092

Post Posted: Mon Sep 03, 2018 9:24 am Reply with quote    Back to top    

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours