Circular Reference with four tables

[kenr169387]

How do I join all four tables to avoid a circular reference?  The goal is to filter by customer and common date to see invoice and sales order data.  I can join 3 out of 4, but the 4th is stumping me.  

Customer:
load * inline [Customer
A
,B
,C
,D
,E
,F
,G
];

Invoice:
load * inline [Customer, Invoice, InvoiceDate, SalesOrderNo
A, 201, 2/1/2022, 101
,A, 202, 2/2/2022, 102
,A, 203, 2/3/2022, 102
,B, 204, 2/3/2022, null
,C, 205, 2/4/2022, 103
,D, 206, 2/4/2022, null
,E, 207, 2/7/2022, 104
];

SalesOrder:
load * inline [Customer, SalesOrderNo, OrderDate
A, 101, 2/1/2022
,A, 102, 2/1/2022
,C, 103, 2/2/2022
,E, 104, 2/2/2022
,F, 105, 2/7/2022
];

Calendar:
load * inline [ActivityDate
2/1/2022
,2/2/2022
,2/3/2022
,2/4/2022
,2/5/2022
,2/6/2022
,2/7/2022
];

• InvoiceDate does not always equal OrderDate and visa versa
• not all SalesOrderNo references an invoice
• Not all invoices reference a SalesOrderNo
• a SalesOrderNo can reference multiple invoices, but invoice can only reference 1 SalesOrderNo
• Where Invoices reference SalesOrderNo they have the same customer

If user filters for Customer A on date 2/1/2022 the result should be 

• SalesOrderNo: 101, 102
• InvoiceNo: 201

if user filters for just date 2/2/2022 the result should be

• SalesOrderNo: 103, 104
• Invoice: 202

Thanks for the help!

[albertovarela · Accepted solution]

You could create a concatenated fact table. Give this a try:

Customer:
load *, Customer as %CustomerKey inline [Customer
A
B
C
D
E
F
G
];

SalesData:
load *,
'Invoice' as RecordType,
InvoiceDate as %ActivityDateKey,
Customer as %CustomerKey
inline [
Customer, Invoice, InvoiceDate, SalesOrderNo
A, 201, 2/1/2022, 101
A, 202, 2/2/2022, 102
A, 203, 2/3/2022, 102
B, 204, 2/3/2022, null
C, 205, 2/4/2022, 103
D, 206, 2/4/2022, null
E, 207, 2/7/2022, 104
];

Concatenate(SalesData) //Append SalesOrder with SalesData Table
load *,
'SalesOrder' as RecordType,
OrderDate as %ActivityDateKey,
Customer as %CustomerKey
inline [
Customer, SalesOrderNo, OrderDate
A, 101, 2/1/2022
A, 102, 2/1/2022
C, 103, 2/2/2022
E, 104, 2/2/2022
F, 105, 2/7/2022
];

DROP Field Customer From SalesData;

Calendar:
load *,
ActivityDate as %ActivityDateKey
inline [
ActivityDate
2/1/2022
2/2/2022
2/3/2022
2/4/2022
2/5/2022
2/6/2022
2/7/2022
];

[kenr169387]

That seems to have it!  It was the RecordType I was missing.   Just out of curiosity, what's the purpose for the % sign in front of the key columns?

Thanks for the help!

[albertovarela]

Good to hear! 

The % is to identify the keys. Normally I would add statement:

set HidePrefix='%' ;

to hide these fields from the UI