Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
joioan
Contributor III
Contributor III

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.

Labels (3)
1 Reply
joioan
Contributor III
Contributor III
Author

Hello 🙂 

Does anyone have a hint for this case or a totally new approach? 

That would help a lot. Thank you!