topic Re: Need Suggestion in QlikView

Need Suggestion

manojkulkarni — Tue, 13 Dec 2016 10:23:38 GMT

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

Re: Need Suggestion

shraddha_g — Tue, 13 Dec 2016 10:27:14 GMT

What is the logic for End date?

Re: Need Suggestion

marcus_sommer — Tue, 13 Dec 2016 10:32:59 GMT

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

Re: Need Suggestion

Anonymous — Tue, 13 Dec 2016 10:36:03 GMT

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

Re: Need Suggestion

manojkulkarni — Tue, 13 Dec 2016 11:05:42 GMT

Next available start date for User

Re: Need Suggestion

manojkulkarni — Tue, 13 Dec 2016 11:09:40 GMT

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

Re: Need Suggestion

sasikanth — Tue, 13 Dec 2016 14:30:47 GMT

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;