script to create records to fill in gaps in history table

[#1]

I am working on building reports for a issue management ticketing system. The ticketing system database does not keep a transactional history of the ticket status, which is making it hard to answer questions in Qlik Sense like : How many tickets were open in Jan-2015, Feb-15? How many tickets were resolved in Mar-2015? etc.

The database does have the necessary data in order to create the necessary data through calculations.

Source Data Table:

Ticket_ID	Open_Date	Resolve_Date	Close_Date
1	1/1/15	3/1/15	3/10/15
2	1/1/15	4/10/15
3	2/10/15

Event Table:

Ticket_ID	Event	Event_Date
1	Opened	1/1/15
1	Resolved	3/1/15
1	Closed	3/10/15
2	Opened	1/1/15
2	Resolved	4/10/15
3	Opened	2/10/15

Does anyone have any advice on how I should best "fill in the gaps" - my idea for the data structure is below (the granularity in reporting does not need to go below month-year), but am open to better ideas.

Measure Table

Ticket_ID	Ticket_Status	Month-Year
1	Open	Jan-2015
1	Open	Feb-2015
1	Resolved	Mar-2015
1	Closed	Mar-2015
2	Open	Jan-2015
2	Open	Feb-2015
2	Open	Mar-2015
2	Open	Apr-2015
2	Resolved	Apr-2015
3	Open	Feb-2015

[swuehl]

You can start with

Creating a Date Interval from a Single Date

then follow

Creating Reference Dates for Intervals

And I also suggest to read the great tech doc:

IntervalMatch and Slowly Changing Dimensions