5 Replies Latest reply: Sep 18, 2014 3:41 AM by Martin Grimmelt RSS

    Turn a time span into several rows

    Martin Grimmelt

      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