Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Turn a time span into several rows

Hi Martin,

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

5 Replies
maxgro
Not applicable

Re: Turn a time span into several rows

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

Generating Missing Data In QlikView

Not applicable

Re: Turn a time span into several rows

Hi Martin,

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

MarcoWedel
Not applicable

Re: Turn a time span into several rows

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
Not applicable

Re: Turn a time span into several rows

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

Re: Turn a time span into several rows

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