topic Re: Generate records for inbetween dates? in QlikView

Generate records for inbetween dates?

Thu, 19 Jun 2014 13:48:55 GMT

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?

Re: Generate records for inbetween dates?

joshabbott — Thu, 19 Jun 2014 13:50:32 GMT

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

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

Re: Generate records for inbetween dates?

Gysbert_Wassenaar — Thu, 19 Jun 2014 14:20:30 GMT

See this document: Generating Missing Data In QlikView

Re: Generate records for inbetween dates?

Thu, 19 Jun 2014 14:21:51 GMT

Debbie,

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

IntervalMatch by Henric Cronström

Regards

Re: Generate records for inbetween dates?

Thu, 19 Jun 2014 14:24:14 GMT

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

Re: Generate records for inbetween dates?

Thu, 19 Jun 2014 14:25:30 GMT

My master calendar is linked to the Shipment Date

Re: Generate records for inbetween dates?

Thu, 19 Jun 2014 14:32:48 GMT

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