CREATE A KEY USING INTERVALMATCH

[hopkinsc]

Hi all, I have a requirement that I'm struggling with. I think i need to use the intervalmatch function but the resulting dates need to be part of a key field which would link to another table. 

I have a Fact table that contains the following fields..

Date

Company,

Source,

Value

Basically, a 'Company' can have multiple sources, but not on the same 'Date', e.g.

Date	Company	Source	Value
07/01/2022	A001	A	105
08/01/2022	A001	A	115
09/01/2022	A001	A	110
10/01/2022	A001	A	100
11/01/2022	A001	B	105
12/01/2022	A001	B	100
13/01/2022	A001	B	105
14/01/2022	A001	B	110

Sot he above shows that from the 07/01/2022 - 10/01/2022, Company A001 used Source A but then from the 11/01/2022 onwards, it used Source B

I then have another table 'Assets', which looks like the following..

Company	Source	StartDate	EndDate
A001	A	15/11/2021	10/01/2022
A001	B	11/01/2022

This shows the start and end date of each Source. 

I am trying to get the StartDate and EndDate from the Asset table into the Fact table. I think i would have to use an INTERVALMATCH but I don't then know how to use that in the Key to join both tables. 

The join will need to be done on 

Company & Source & Date in the Fact table

and

Company & Source & IntervalMatch Date in the Asset table

Any help would be appreciated. 

Samples attached

thanks

[hic]

You will need to use

IntervalMatch (Date, Company, Source) Load ...

and you will need to make sure to remove redundant keys. 

This is basically the same problem as you have in Slowly Changing Dimensions. Take a look at 

https://community.qlik.com/t5/Design/Slowly-Changing-Dimensions/ba-p/1464187

and you will get ideas how to solve this. See also

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/IntervalMatch_(Ex...

[hopkinsc]

Hi Hic,

Thanks for your reply. 

I've followed this link

IntervalMatch and Slowly Changing Dimensions - Qlik Community - 1479928

and downloaded the ZIP and followed the example in 'Slowly Changing Dimensions'  but I can't seem to get it to work for Source B, Source A is appearing correct but there are no dates against source B.

Here is my script.

// SalespeopleDyn
Asset:
load autonumber(Company_Asset & '|' & Source_Asset & '|' & StartDate & '|' & EndDate) as Key1,
*
;
LOAD
Company as Company_Asset,
Source as Source_Asset,
Company & '|' & Source as Key,

Date(StartDate) as StartDate,
Date(EndDate) as EndDate
FROM [lib://Downloads/Sample.xlsx]
(ooxml, embedded labels, table is Asset);

// Transactions
Fact:
LOAD
"Date",
Company,
Company & '|' & Source as KeyTMP,
Source,
Value
FROM [lib://Downloads/Sample.xlsx]
(ooxml, embedded labels, table is Fact);

// TmpBridgeTable
intervalMatch:
IntervalMatch(Date, KeyTMP)
Load
StartDate,
EndDate,
Key as KeyTMP

resident Asset;

left join (Fact)
Load KeyTMP,
Date,
autonumber(KeyTMP &'|'& StartDate &'|'& EndDate) as Key1
resident intervalMatch;

Drop table intervalMatch;
drop field KeyTMP;

and this is my output..

Are you able to see if there is something obvious I'm doing wrong please?

Thanks

[hic]

Your code works fine! But since you have a blank EndDate in the source data, you get strange results...

Try

Date(Coalesce(StartDate,MakeDate(2000))) as StartDate,
Date(Coalesce(EndDate,Today())) as EndDate

instead of 

Date(StartDate) as StartDate
Date(EndDate) as EndDate