Calculating new Field problem

[#1]

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!

[swuehl · Accepted solution]

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

[#3]

Hi Stefan,

Thank you so much! That did the trick!!!  Much appreciated!

Thanks,

-Marek