Fact table link to scd type 2

[Jsobrinho77]

Hi guys,

I have a fact table in which I have the Sales by the company and date, but I have another table with the company, KPI and the start/enddate from the KPI.

So, my question is: How can I link the fact table with the SCD type 2 table?

Fact columns table:
Company, Date, Sales, key=Company & Date

SCD_2 columns table:
Company, KPI,Startdate, Enddate, value, key=Company?? ( which date do I need to use when I filter Fact Date column )

[ogster1974]

You need to turn your SCD_2 columns table into 

Company, KPI, Date, key=Company & Date 

Structure

Need to loop through your dataset for each change in Company & Start Date create a new date field based on the Start and End Date fields.  You can then create your key field that will align with your fact table joining KPI and Sales for the given period.

[Jsobrinho77]

First of all, thank you for your answer...

Could you please post an example? I didn't understand.

[Anonymous]

Can you share a sample data set containing just a few rows from both tables and the desired output.

[Jsobrinho77]

Hi,

Here is my file. So, I need to link this with my fact table. In fact table I have 

CodCompany1

CodCompany2

and 
Date column

[Gabbar]

Sales:
LOAD
Company,
SalesDate
FROM (Source);

CompanyKPI:
LOAD
Company,
KPI,
StartDate,
EndDate
FROM [lib://DataFiles/Customers.qvd]
(qvd);

Left Join(Sales)
IntervalMatch(SalesDate,Company)
Load StartDate, EndDate,Company
Resident CompanyKPI;

Now you can either create a key on company,startdate and enddate to link them or you can join them,
as you wish.

If this Resolves your query please accept this as an answer.

[Jsobrinho77]

Thank you, I think it's almost there...

I'm sorry, but I'm trying to remove the synthetic key, but I can't, could you please help me?

Table:
LOAD * Inline [
CodCompany , Date,Sales
55508231 , 2023-01-01 00:00:00.0,400
55508231 , 2023-02-01 00:00:00.0,500
56508232 , 2022-02-01 00:00:00.0,600
57508233 , 2021-02-01 00:00:00.0,700
];

KPI:
LOAD * Inline [
CodCompany, KPI, Start, End, Value,
55508231, MinimumTargetDFG, 1900-01-01 00:00:00.0, 9999-01-01 00:00:00.0, 0.14
55508231, MinimumPurchase, 1900-01-01 00:00:00.0, 9999-01-01 00:00:00.0, 0.75
55508231, MinimumWholesaler, 1900-01-01 00:00:00.0, 9999-01-01 00:00:00.0, 0.75
55508231, WeightDistribution, 1900-01-01 00:00:00.0, 2023-01-01 00:00:00.0, 0.6
55508231, WeightDistribution, 2023-02-01 00:00:00.0, 9999-01-01 00:00:00.0, 0.7
55508231, WeighDistributionDepartment, 1900-01-01 00:00:00.0, 9999-01-01 00:00:00.0, 1
55508231, WeightDistributionType, 1900-01-01 00:00:00.0, 9999-01-01 00:00:00.0, 0.7
56508232, MinimumTargetDFG, 1900-01-01 00:00:00.0, 9999-01-01 00:00:00.0, 0.11
56508232, MinimumPurchase, 1900-01-01 00:00:00.0, 9999-01-01 00:00:00.0, 0.12
56508232, MinimumWholesaler, 1900-01-01 00:00:00.0, 9999-01-01 00:00:00.0, 0.13
56508232, WeightDistribution, 1900-01-01 00:00:00.0, 2023-01-01 00:00:00.0, 0.14
56508232, WeightDistribution, 2023-02-01 00:00:00.0, 9999-01-01 00:00:00.0, 0.15
57508233, WeighDistributionDepartment, 1900-01-01 00:00:00.0, 9999-01-01 00:00:00.0, 11
57508233, WeightDistributionType, 1900-01-01 00:00:00.0, 9999-01-01 00:00:00.0, 0.16

];

left join IntervalMatch(Date,CodCompany)
LOAD Start,End,CodCompany
Resident KPI;

[Gabbar · Accepted solution]

create a key in both the tables and the drop the respective fields from dimension table.
lets take your KPI table as Fact table and [Table] table as Dimension table;

After your your script do this:
noconcatenate
A:
load CodCompany&Date as %key_company,Sales resident Table;
Drop Table Table;
noconcatenate
B:
load *,CodCompany&Date as %key_company resident KPI;
Drop Table KPI;