How to create a common date bridge for 2 tables associated with a link table
Hello everyone.
I'm faced with a new challenge, in which i find a lot of interest.
The fact tables share a many to many relationship so i created a link table for them which I will provide.
I would like to create a common date bridge table using the KeyDate logic, but now the tables are not joined.
How should i do this?
The script so far is:
TempMasterfactTable1:
LOAD
"ID1" ,
date(floor("Date1"),'DD/MM/YYYY') as "Date1",
date(floor("Date2"),'DD/MM/YYYY') as "Date2",
FROM [$(vPathQVD)T1.qvd]
(qvd);
right join(TempMasterfactTable1)
LOAD
"ID1",
"ID2",
date(floor("Date3"),'DD/MM/YYYY') as "Date3" ,
date(floor("Date4"),'DD/MM/YYYY') as "Date4",
FROM [$(vPathQVD)T2.qvd]
(qvd);
NoConcatenate
Temp_Table:
LOAD *,"ID1"&"ID2" as FamilyKey,
resident TempMasterfactTable1;
drop table TempMasterfactTable1;
T2:
LOAD
Ticket_AA,
"ID11",
"ID21"
"ID11"&"ID21" as FamilyKey,
date(floor("Date5"),'DD/MM/YYYY') as "Date5"
FROM [$(vPathQVD)T2.qvd]
(qvd);
link_table:
NoConcatenate
LOAD DISTINCT
"ID1"&"ID2" as FamilyKey,
"ID1",
"ID2"
resident Temp_Table;
Concatenate(link_table)
LOAD DISTINCT
"ID11"&"ID21" as FamilyKey,
"ID11" as ID1,
"ID21" as ID2
Resident T2;
DROP Fields "ID1","ID2" from Temp_Table;
DROP Fields "ID11","ID21" from T2;
previously (when i didn't take into account the many to many relationship and i just left joined t2 with tempmasterfacttable1) i followed this logic with Keydate:
TempMasterfactTable:
LOAD *, floor("Date1")&'|'&floor("Date2")&'|'&floor("Date3")&'|'&floor("Date4")&'|'&floor("Date5") as KeyDate
Resident TempMasterfactTable1;
Drop Table TempMasterfactTable1;
Date_Bridge: Load KeyDate, date(SubField(KeyDate,'|',1)) as CanonicalDate, 'DateType1' as DateType; Load FieldValue('KeyDate',RecNo()) as KeyDate AutoGenerate FieldValueCount('KeyDate');
Concatenate(Date_Bridge) Load KeyDate, date(SubField(KeyDate,'|',2)) as CanonicalDate, 'DateType2' as DateType; Load FieldValue('KeyDate',RecNo()) as KeyDate AutoGenerate FieldValueCount('KeyDate');
Concatenate(Date_Bridge) Load KeyDate, date(SubField(KeyDate,'|',3)) as CanonicalDate, 'DateType3' as DateType; Load FieldValue('KeyDate',RecNo()) as KeyDate AutoGenerate FieldValueCount('KeyDate');
Concatenate(Date_Bridge) Load KeyDate, date(SubField(KeyDate,'|',4)) as CanonicalDate, 'DateType4' as DateType; Load FieldValue('KeyDate',RecNo()) as KeyDate AutoGenerate FieldValueCount('KeyDate');
Concatenate(Date_Bridge) Load KeyDate, date(SubField(KeyDate,'|',5)) as CanonicalDate, 'DateType5' as DateType; Load FieldValue('KeyDate',RecNo()) as KeyDate AutoGenerate FieldValueCount('KeyDate');
autonumber KeyDate;
Please help me on this, i am really stuck. Thank you in advance 🙂 It means a lot.
Hello @joioan did you get a solution to this. I am experiencing the same challenge. I have two tables with different dates and need a common filter to affect the two.
In many common scenarios it's not necessary to create link-tables else it's more suitable to concatenate the facts by harmonizing the field-names and data-structure as much as possible.
Data like actual & budget & forecast are mainly the same as well as order & billing & shipment - only the point of view is different ...
For my exact scenario, I have Loan information and Customer Registration. From Loans, I have the issue date and from Customer I have the registration Date. The goal is to have a common Date which once filtered will affect both loans and customer information i.e A 2025 filter will return Loans Issued in 2025, and customers registered in the same year.
I have tried giving the same alias to the two dates then doing a concatenate as well as having a date bridge but the two approaches didn't work.
