Need Suggestion

[manojkulkarni]

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

[shraddha_g]

What is the logic for End date?

[marcus_sommer]

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

[Anonymous]

Do these projects have different durations? You can estimate a time duration and try to automate the completion of the end date.

[manojkulkarni]

Next available start date for User

[manojkulkarni]

There is no exact duration specified. It is only noticed when user change to next project

[sasikanth]

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;