topic Turn a time span into several rows in QlikView

Turn a time span into several rows

Wed, 17 Sep 2014 06:42:38 GMT

Hi there,

I have been worrying about the following problem for quite some time, but a real solution has not come up so far.

There is the following table Tasks:

TaskID	TaskName	Start-Date	End-Date	Total Work	Work per Day
1	Writing the concept	Januar 1, 2014	January 5, 2014	20h	4h
2	Implement the Code	January 6, 2014	January 15, 2014	80h	8h
3	Testing	January 16, 2014	January 18, 2014	18h	6h

There is the need for a forecasting-table that shows how much work there is for each day in a time-span. With that, we want to do calculations on how much work there is each day for each business unit, department, employee, ...

The data model is comparable to MS Project Server.

I would like to have the following target-table Task-Work-Per-Day:

TaskID	Day	Work
1	January 1, 2014	4h
1	January 2, 2014	4h
1	January 3, 2014	4h
1	January 4, 2014	4h
1	January 5, 2014	4h
2	January 6, 2014	8h
2	January 7, 2014	8h
2	January 8, 2014	8h
2	January 9, 2014	8h
2	January 10, 2014	8h
2	January 11, 2014	8h
2	January 12, 2014	8h
2	January 13, 2014	8h
2	January 14, 2014	8h
2	January 15, 2014	8h
3	January 16, 2014	6h
3	January 17, 2014	6h
3	January 18, 2014	6h

There are ways in SQL to this (e.g. sql - Get time span from two rows in same column - Stack Overflow), but these have not worked out for me since there can be time spans over several years that have led to error messages using the example given. The use of the PIVOT-function within SQL-Server was not succesful either.

I have also tried writing a stored procedure using temporary tables. This has worked out somehow, but the performance is horrible.

I have also tried to use DO-LOOP and FOR-NEXT within Qlikview, but the performance was even worse.

Do you have any idea how to solve this in a performant manner within Qlikview? Your help is greatly appreciated.

Thanks in advance.

Martin

Re: Turn a time span into several rows

maxgro — Wed, 17 Sep 2014 21:18:09 GMT

maybe here, download pdf, goto page 10 for the example

Generating Missing Data In QlikView

Re: Turn a time span into several rows

Wed, 17 Sep 2014 21:54:21 GMT

Hi Martin,

Creating a Master Calendar and IntervalMatch is the key to the solution. See the attached application for ref.

Re: Turn a time span into several rows

MarcoWedel — Wed, 17 Sep 2014 22:19:08 GMT

TaskID	TaskName	Start-Date	End-Date	Total Work	Work per Day
1	Writing the concept	January 1, 2014	January 5, 2014	20h	4h
2	Implement the Code	January 6, 2014	January 15, 2014	80h	8h
3	Testing	January 16, 2014	January 18, 2014	18h	6h

typo correction

Re: Turn a time span into several rows

MarcoWedel — Wed, 17 Sep 2014 22:26:50 GMT

Hi,

one possible solution:

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

tabInput:    
LOAD TaskID,
     TaskName,
     Date#([Start-Date],'MMMM D, YYYY') as [Start-Date],
     Date#([End-Date],'MMMM D, YYYY') as [End-Date],
     Num#([Total Work], '#0h') as [Total Work],
     Num#([Work per Day], '#0h') as [Work per Day]
FROM [http://community.qlik.com/thread/133645] (html, codepage is 1252, embedded labels, table is @3);

tabOutput:
LOAD TaskID,
     Date([Start-Date]+IterNo()-1,'MMMM D, YYYY') as Day,
     [Work per Day] as Work
Resident tabInput
While [Start-Date]+IterNo()-1<=[End-Date];

hope this helps

regards

Marco

Re: Turn a time span into several rows

Thu, 18 Sep 2014 07:41:08 GMT

Thank you guys! This is exactly what I was looking for! Either way has worked fine for me.