Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Martin,
Creating a Master Calendar and IntervalMatch is the key to the solution. See the attached application for ref.
maybe here, download pdf, goto page 10 for the example
Hi Martin,
Creating a Master Calendar and IntervalMatch is the key to the solution. See the attached application for ref.
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
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
Thank you guys! This is exactly what I was looking for! Either way has worked fine for me.