topic Re: CREATE A KEY USING INTERVALMATCH in App Development

CREATE A KEY USING INTERVALMATCH

hopkinsc — Mon, 22 Apr 2024 11:44:26 GMT

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

Re: CREATE A KEY USING INTERVALMATCH

hic — Mon, 22 Apr 2024 11:57:58 GMT

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_(Extended_Syntax).htm

Re: CREATE A KEY USING INTERVALMATCH

hopkinsc — Mon, 22 Apr 2024 14:42:03 GMT

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

Re: CREATE A KEY USING INTERVALMATCH

hic — Mon, 22 Apr 2024 18:55:30 GMT

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