Forum Discussion
Join three tables where no fields match (full outer?)
I have three tables, example:
Table 1 shows the normal weekly quantity allowed on for a particular origin-destination combination. e.g., UK to DE normally we are only allowed to send 8 units.
Table 2 shows that for some weeks we might have an exception. Example IE to BE for weeks 52 and 01 (christmas holidays!) we are not allowed to send any units (ZERO units). Table 2 ONLY shows exception weeks - not all weeks.
Table 3 shows the week range of the total data set. i.e. ALL weeks
Previously I was just multiplying the QTY by the number of weeks to get a total, but the new exceptions (table 2) means that I can't do that any more. It is also important that I am able to show this by week as well as by total, as it goes into a lvely line chart over time. So I am looking for a way to join these tables together to give me something like this, where all data in table 1 is shown against all weeks:
(which is horrible proliferation, I know, but my actual Table 1 is only 2K rows so if I proliferate even for a full year of 52 weeks it's still not that bad!)
Hi dapperscavenger ,
I would start with table3 and add a custom column where you reference table1.
That will return everything from table1 for each week.
Then join table2 to it based on "From", "To", and "week".
Then add a custom column with the condition that if the value of the expansion from table2 is null, then take the expansion from table1, else take expansion from table2.
2 Replies
Hi dapperscavenger ,
I would start with table3 and add a custom column where you reference table1.
That will return everything from table1 for each week.
Then join table2 to it based on "From", "To", and "week".
Then add a custom column with the condition that if the value of the expansion from table2 is null, then take the expansion from table1, else take expansion from table2.Brilliant, yes, it was just that first step I was stuck on. I didn't know you could reference another column like that
Had to look up how to write it but it was fairly straightforward: =#"table_name_here"
Thank you
