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:
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.
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.