Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an issue where I need to be able to have multiple date, Order Date, Ship Date, Payment Date.
In my example, I have an Order that has an Order Date in one table, in addition a ship date in another table, and a payment date in another table. I need to link all tables by order number. However I need to be able to have a "Transaction Date" so I can see what happened in a particular month.
For example, Order #123 has an Order Date of 07/31/2017, ShipDate of 08/01/2017, and a PaymentDate of 08/01/2017.
Order #789 has an order date of 07/30/2017, ShipDate of 08/01/2017, and Payment Date of 07/31/2017.
I need to be able to both filter the report on each date, which I am able to do with not problem, but I also want to create a "Transaction Date", that I can filter on. So if I create a "Transaction Date", and say show me everything that happened in the month of August, It would should be the Order#123 because it has transactions (SHIPDATE and PAYMENTDATE) and it would show me Order#789 because it also has transactions (ShipDate).
I can't seem to do this without it creating a loop.
Thoughts?
Hi Lance,
Your best bet is to create a link table to join all three tables.
Your link table will have following:-
Order No
Date
Combination of Order No and Order Date as a key to connect with Orders table
Combination of Order No and Ship Date as a key to connect with Shipping table
Combination of Order No and Payment Date as a key to connect with Payments table
Your individual tables will have the relevant key and not the Order No.
Hope this helps.
Thanks & Best Regards,
Kuldeep Tak
Hi,
Have you thought of using a canonical date bridge?
Its basically a master calendar for your dates! I can just select one date, and it will show everything that was opened and closed on that date.
This is mine:
Date Bridge:
Load
query_id,
query_start_date as CanonicalDate,
'QueryStartDate' as DateType
Resident Queries;
Load
query_id,
query_date_last_changed as CanonicalDate,
'QueryEndDate' as DateType
Resident Queries;
CanonicalTempMinMax:
Load
Min(CanonicalDate) as CanonicalMinDate,
Max(CanonicalDate) as CanonicalMaxDate
Resident DateBridge;
LET vCanonicalMinDate = Peek('CanonicalMinDate',0,'CanonicalTempMinMax');
LET vCanonicalMaxDate = Peek('CanonicalMaxDate',0,'CanonicalTempMinMax');
LET vCanonicalToday = $(vCanonicalMaxDate);
//***************************************Temp Calendar
CanonicalTempCal:
Load
date($(vCanonicalMinDate) + rowno()-1) as CanonicalTempDate
AutoGenerate
$(vCanonicalMaxDate) - $(vCanonicalMinDate)+1;
Drop Table CanonicalTempMinMax;
//**************************************Master Calendar
Canonical_MasterCalendar:
LOAD
Floor(CanonicalTempDate) As CanonicalDate,
Week(CanonicalTempDate) As Canonical_Week,
Year(CanonicalTempDate) As Canonical_Year,
Month(CanonicalTempDate) As Canonical_Month,
Day(CanonicalTempDate) As Canonical_Day,
Weekday(CanonicalTempDate) As Canonical_Weekday,
'Q'&ceil(month(CanonicalTempDate)/3) As Canonical_Quarter,
Date(monthstart(CanonicalTempDate), 'MMM-YYYY') As Canonical_MonthYear,
Week(CanonicalTempDate)&'-'&Year(CanonicalTempDate) As Canonical_WeekYear,
inyeartodate(CanonicalTempDate,$(vCanonicalToday),0)*-1 As Canonical_CurYTDFlag,
inyeartodate(CanonicalTempDate,$(vCanonicalToday),-1)*-1 As Canonical_LastYTDFlag,
inmonthtodate(CanonicalTempDate,$(vCanonicalToday),0)*-1 As Canonical_CurMTDFlag,
inmonthtodate(CanonicalTempDate,$(vCanonicalToday),-1)*-1 As Canonical_LastMTDFlag
Resident CanonicalTempCal
Order By CanonicalTempDate ASC;
Drop Table CanonicalTempCal;