Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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