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.
