Hi everyone,
I'm attempting to create an hourly gantt chart in Qlikview but keep running into a few problems.
My data are structured in three columns:
id - unique identifier of a particular task
due_date - date which a task should be completed. Given as a time stamp
hours - expected number of hours to complete each task
In the script, I'm creating the start date of each task by subtracting hours from due_date. I'm then using three for loops to iterate through each id, date, and hour. My goal is to assign a 1 to each hour of each day that a task should be worked on and a 0 otherwise.
A work day is assumed to run from 10:00am to 6:00pm. For example, if a task is due on 10/9 at 12:00pm and is expected to take 6 hours to complete I want to create the following data table:
| Date | Hour | Work? |
|---|
| 10/8 | 10:00pm | 0 |
| 10/8 | 11:00pm | 0 |
| 10/8 | 12:00pm | 0 |
| 10/8 | 1:00pm | 0 |
| 10/8 | 2:00pm | 1 |
| 10/8 | 3:00pm | 1 |
| 10/8 | 4:00pm | 1 |
| 10/8 | 5:00pm | 1 |
| 10/9 | 10:00am | 1 |
| 10/9 | 11:00am | 1 |
| 10/9 | 12:00pm | 0 |
| 10/9 | 1:00pm | 0 |
| 10/9 | 2:00pm | 0 |
| 10/9 | 3:00pm | 0 |
| 10/9 | 4:00pm | 0 |
| 10/9 | 5:00pm | 0 |
Ideally, once I have this table as output, I will be able to create a pivot table and color each cell based on the 1 or 0 value. The output does not necessarily have to be a pivot table, I just assumed that would be the easiest to create.
My script executes without any errors but I'm having trouble with:
- Duplication of data
- Creating a logical expression to assign 1 to the hour which a task should be worked on
The due dates in my data set range from a few hours to a few days. When the script executes, it duplicates data by creating a row for each hour of each day for every id in the data set.
I attempted to recreate the script layed out in the blog below.
How to vizualize a work schedule using a Qlikview Gantt chart | One data at a time
I've attached a sample qvw file with my script and an Excel spreadsheet with a small subset of data and the desired output.
Any suggestions or feedback would be greatly appreciated.
Thanks!
Michael
)