topic Calculating new Field problem in QlikView

Calculating new Field problem

Mon, 21 Nov 2011 22:54:28 GMT

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!

Calculating new Field problem

swuehl — Mon, 21 Nov 2011 23:39:03 GMT

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

Calculating new Field problem

Tue, 22 Nov 2011 21:23:46 GMT

Hi Stefan,

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

Thanks,

-Marek