Creating project phase end date from the start date of the next one

[#1]

Hello community!

I am trying to create project phase end dates using the start date of the next phase. Problem is that the projects don't include same phases and the data structure is a little bit mesy.

To clarify a bit, I have fields

Project ID     Project Phase       Start Date

111                 Phase 1              2015-01-15

111                 Phase 4              2015-02-15

111                 Phase 5              2015-02-16

111                 Closure               2015-03-01

I want to add the end date information

Project ID     Project Phase       Start Date           End Date

111                 Phase 1              2015-01-15        2015-02-15

111                 Phase 4              2015-02-15        2015-02-16

111                 Phase 5              2015-02-16        2015-03-01

111                 Closure               2015-03-01    

I have created a Project Phase Sort field that gives numbers from 1-10 to the phases in right order. Can I use this sort field to find out which is the next phase and the right end date? In some cases the project can go from phase 1 to 8 directly.        

Thanks for your help!

Regards,

Heidi

[Gysbert_Wassenaar]

Temp:

LOAD * FROM ...source...;

Result:

LOAD *, if([Previous([Project Id]=[Project Id],previous([Start Date])) as [End Date]

RESIDENT Temp

Order by [Project Id], [Start Date] desc;

Drop Table Temp;

[#3]

Thanks Gysbert!

It seems to be working in some extent! Is it any way possible to exploit the phase sort order numbers from one to ten? There is still a problem when two phases have the same date. For example when phases one and two have the same Start Date, it makes phase 1 to have the End Date from phase 3 when it should take it from phase 2..

Thanks again!

Regards,

Heidi

[preminqlik]

try this

source:

LOAD * FROM ...source...;

EnddateTable:

LOAD distinct [Start Date],  previous([Start Date])   as [End Date]

RESIDENT source

Order by  [Start Date] desc;

FINAL_RESULT:

join (source)

Load [Start Date],[End Date]

resident EnddateTable;

drop table EnddateTable;

[Gysbert_Wassenaar · Accepted solution]

Ok, try this:

MapPhase:
Mapping LOAD * INLINE [
Phase, Number
Phase 1, 1
Phase 2, 2
...
Phase 29, 29
Closure, 30
];

Temp:
LOAD *, applymap('MapPhase',[Project Phase]) as PhaseOrder FROM ...source...;

Result:
LOAD *, if([Previous([Project Id]=[Project Id],previous([Start Date])) as [End Date]
RESIDENT Temp
Order by [Project Id], [Start Date] desc, PhaseOrder desc;

Drop Table Temp;