Generate records for inbetween dates?

[#1]

I have a list of contracts that may or may not change throughout the year.  I want to create a row for the months where the contract did not change.  i.e.

Contract ID   Effective Date  Amount  MonthStart (used in contractkey)    

1                   01/10/2014       5000    01/01/2014

1                   02/03/2014       4500    02/01/2014

1                   05/15/2014       4650    05/01/2014

Details

FactID          ContractID         FactDate       MonthStart(used in contractkey)

45                    1                   01/12/2014    01/01/2014

46                     1                   04/12/2014   04/01/2014

The  FactID 46 will not have a contract linked to it.  I can either create a row for the months in between using monthstart (but need to understand how to do this) OR use some sort of interval match to pick up the contracts in between but not exactly sure how to make this work.  I need to know the amount to charge based on contract amount.  Sometimes a contract can change twice in one month which further complicates things BUT this is not typical so we simply pull the last entry per month to bring each contract down to just one row per month and use MonthStart in the key. 

The Contract key is a combination of 6 different fields to include the Monthstart.  It is working except where there is no contract entry for that month.  such as the APril 2014 entry in the example above.

Any ideas?

[joshabbott]

Sounds like you need to create a master calendar table in your QVW, check out this post:

http://community.qlik.com/thread/48693

[Gysbert_Wassenaar]

See this document: Generating Missing Data In QlikView

[#4]

Debbie,

I think you will need to use interval match, with a master calendar.

IntervalMatch by Henric Cronström

Regards

[#5]

I have a master calendar but not sure that will solve my problem.

I have shipment details by date and these are linked to the Master calendar.

I have contract details but they do not exist for every date, only those dates where the contract has changed.  This contract data is linked by a key to the shipment details.

My question:  How do I get the contract amount assigned to the shipment detail record based on a date range?

I can use the peek function to get the from and to dates and the amount from the contract table by ID.  I then have a table:

Contract ID  From Date      To Date      Amount

1                  01/10/2013      02/10/2013    5000

1                  02/11/2013     Current           4500

Shipment Details

FactID     ContractID     Shipment Date  Contract Amount

45              1                 02/02/2013          should be 5000

46               1                 03/25/2013         should be 4500

I want to assign the contract amount if the date is within the range of the from and to date

[#6]

My master calendar is linked to the Shipment Date

[#7]

You may need another linked to contract date.  We often have many, although this may also help Canonical Date