Connect 2 fact tables with intervals

kavicsgym — Sun, 12 Oct 2014 21:43:24 GMT

Hello, I have 3 tables and not sure which way to build up the data model:

FACT table: mortgage loan data for 8 months is concatenated here:

• Contract ID,

• Person ID,

• Period (Jan, Feb. March...), - the same contracts are listed multiple times for diff months

• Monthly installment – the amount payed by the debtor that month

• Remining capital that month

• Interest rate

Contract DIM table about the detailes of the loan contracts:

• Contract ID, • Person ID, • Period (Jan, Feb. March...),

• Start of contract

• End of contract

• Currency of contract

• Total Amount of the contract

• Type of contract, .. other descriptive value

Fact 2. table about the non-performing contracts:

• Contract ID, • Person ID,

• Start of non-payment

• End of non-payment

• Not paid amount

Please note that between Fact table and Fact 2. table there is a many-to-many relationship. The same contract is listed for each month in Fact, and the same contract may have multiple entries in Fact 2 with different period and different not paid amount.

I have to calculate avarage interest rate per month separately for performing and non-performing contracts. I tried interval match between Fact and Fact2 (it works) but I do not know how to differentiate between the performing and non – performing contracts in charts.

I am not sure how to connect Fact and Fact 2.

Please advise, thanks a lot!!! Enikö

topic Connect 2 fact tables with intervals in QlikView

Connect 2 fact tables with intervals