Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Turn a time span into several rows

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:

TaskIDTaskNameStart-DateEnd-DateTotal WorkWork per Day
1Writing the conceptJanuar 1, 2014January 5, 201420h4h
2Implement the CodeJanuary 6, 2014January 15, 201480h8h
3TestingJanuary 16, 2014January 18, 201418h6h

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:

TaskIDDayWork
1January 1, 20144h
1January 2, 20144h
1January 3, 20144h
1January 4, 20144h
1January 5, 20144h
2January 6, 20148h
2January 7, 20148h
2January 8, 20148h
2January 9, 20148h
2January 10, 20148h
2January 11, 20148h
2January 12, 20148h
2January 13, 20148h
2January 14, 20148h
2January 15, 20148h
3January 16, 20146h
3January 17, 20146h
3January 18, 20146h

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

1 Solution

Accepted Solutions
Not applicable
Author

Hi Martin,

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

View solution in original post

5 Replies
maxgro
MVP
MVP

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

Generating Missing Data In QlikView

Not applicable
Author

Hi Martin,

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

View solution in original post

MarcoWedel

TaskIDTaskNameStart-DateEnd-DateTotal WorkWork per Day
1Writing the conceptJanuary 1, 2014January 5, 201420h4h
2Implement the CodeJanuary 6, 2014January 15, 201480h8h
3TestingJanuary 16, 2014January 18, 201418h6h

typo correction

MarcoWedel

Hi,

one possible solution:

QlikCommunity_Thread_133645_Pic2.JPG.jpg

QlikCommunity_Thread_133645_Pic1.JPG.jpg

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

Not applicable
Author

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