Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I am fairly new to QlikView coming from a MySQL development world. I have the following problem and I cannot figure out how to solve it in QlikView.
I have two tables, one table called Projects, and the other ProjectStatusHistory. They are linked via a ID. The ProjectStatusHistory table holds a history of all of that stages that the project went through, and a timestamp for each of those stages. Each project can have a handful of stages associated with it through out its lifetime.
What I need to create in a additional field in the ProjectStatusHistory that calculates how long the project stayed in each stage.
Example:
ProjectID Status DateTime Days
1 Created May/15/2011 0
1 InReview May/20/2011 5
1 Accepted June/1/2011 11
1 InDevelopment June/5/2011 4
2 ProjectReview Jan/29/2011 0
2 BugFixing Jan/30/2011 1
3 QATesting Feb/15/2011 16
Does anyone have any ideas on how during the loading of this data from an XML file can I create the Day field with the calculated duration between status updates? I just cannot wrap my head around this problem, and the "QlikView" was of doing it.
Thanks!
Hi,
you can do this by loading your Project data ordered by ID and DateTime (DateTime should be a recognized date time with numerical representation, you might need to parse the DateTime in with date# or set the standard date time accordingly), then use peek() function to calculate the delta in days between project stages, maybe like:
Projects:
LOAD ProjectID, Status, Date(Date#(DateTime,'MMM/DD/YYYY')) as DateTime INLINE [
ProjectID, Status, DateTime
1, Created, May/15/2011
1, InReview, May/20/2011
1, Accepted, Jun/1/2011
1, InDevelopment, Jun/5/2011
2, ProjectReview, Jan/29/2011
2, BugFixing, Jan/30/2011
2, QATesting, Feb/15/2011
];
ProjectDays:
LOAD ProjectID, Status, DateTime, if(Peek(ProjectID)=ProjectID, DateTime-peek(DateTime),0) as Days
resident Projects order by ProjectID, DateTime asc;
drop table Projects;
Hope this helps,
Stefan
Hi,
you can do this by loading your Project data ordered by ID and DateTime (DateTime should be a recognized date time with numerical representation, you might need to parse the DateTime in with date# or set the standard date time accordingly), then use peek() function to calculate the delta in days between project stages, maybe like:
Projects:
LOAD ProjectID, Status, Date(Date#(DateTime,'MMM/DD/YYYY')) as DateTime INLINE [
ProjectID, Status, DateTime
1, Created, May/15/2011
1, InReview, May/20/2011
1, Accepted, Jun/1/2011
1, InDevelopment, Jun/5/2011
2, ProjectReview, Jan/29/2011
2, BugFixing, Jan/30/2011
2, QATesting, Feb/15/2011
];
ProjectDays:
LOAD ProjectID, Status, DateTime, if(Peek(ProjectID)=ProjectID, DateTime-peek(DateTime),0) as Days
resident Projects order by ProjectID, DateTime asc;
drop table Projects;
Hope this helps,
Stefan
Hi Stefan,
Thank you so much! That did the trick!!! Much appreciated!
Thanks,
-Marek