Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
If user filters for Customer A on date 2/1/2022 the result should be
if user filters for just date 2/2/2022 the result should be
Thanks for the help!
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
];
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
];
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!
Good to hear!
The % is to identify the keys. Normally I would add statement:
set HidePrefix='%' ;
to hide these fields from the UI