Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
I have below data with start dates available, i need to derive end date for users by looking at start date of project change. In below data, i have put end date manually which needs to be derived. Please suggest
UserID | UserName | Project | Start Date | End Date |
100 | User1 | Project1 | 01-02-2016 | 01-04-2016 |
101 | User2 | Project1 | 01-02-2016 | |
102 | User3 | Project1 | 01-02-2016 | 01-08-2016 |
103 | User4 | Project1 | 01-02-2016 | |
100 | User1 | Project2 | 01-04-2016 | 01-07-2016 |
100 | User1 | Project1 | 01-07-2016 | 13-12-2016 |
102 | User3 | Project3 | 01-08-2016 |
What is the logic for End date?
Within a to UserID and Project sorted load you could query and evalaute the values from the previous record per Peek() or Previous() ? and evaluate your needed result appropriate.
- Marcus
Do these projects have different durations? You can estimate a time duration and try to automate the completion of the end date.
Next available start date for User
There is no exact duration specified. It is only noticed when user change to next project
HI Manoj,
try this Script below
TEMP:
Load * Inline [
UserID ,UserName ,Project, StartDate
100, User1, Project1, 42371
101, User2, Project1, 42371
102, User3 ,Project1, 42371
103, User4, Project1, 42371
100, User1, Project3, 42376
100 , User1 ,Project2, 42373
102 ,User3 ,Project3, 42377
];
TEST:
LOAD * ,IF(UserID=Previous(UserID) and UserName=Previous(UserName)and Project<>Previous(Project) ,previous(StartDate)) as EndDate
resident TEMP ORDER BY UserID,UserName,Project desc ;
DROP Table TEMP;