Create a link table between 2 tables with missing data

[Russ]

Hi - New to link tables and I have 2 tables with 3 common fields (Date, Customer, Product - but with different names) and want to be able to show all of the records from each in a combined chart. 

Table1:

Date	Customer_Name	Product_Name
1/1/2019	A	A
2/1/2019	B	C
2/1/2019	B	A
3/1/2019	C	B

Table2:

Date	Customer	Product
1/1/2019	A	B
3/1/2019	B	A
3/1/2019	B	C
4/1/2019	D	E

Want to be able to show all records for each table by month in a chart without creating synthetic keys.

Thank you!

[Lisa_P]

You should be able to concatenate these tables into one table:
Load  Date,
            Customer_Name as Customer,
            Product_Name as Product
From ....Table1;

Load  Date,
            Customer,
            Product
From ....Table2;

Once the field names are the same they will automatically concatenate into one table.

[Channa]

Use "Concatenate" between two tables

[Russ]

Thanks Lisa!

Apologies, I should have noted that these are both larger tables with no other matching fields apart from those 3 that match. Want to group them by month and show all potential records (e.g. 3 for March) not just where they match in the Analysis. Is this possible with the concatenation below?

Tried creating a unique key on Date-Customer-Product, but only shows where they match and misses those that aren't a match but in that month.

Thank you!

[Lisa_P · Accepted solution]

So you will need a link table then.  Here is the process:

Ensure original tables do not use common names.
Table1:
Load Date&'|'&Customer_Name&'|'&Product_Name as DateCustProd_Key,
  Date as T1Date,
  Customer_Name,
  Product_Name,
...
Table2:
Load Date&'|'&Customer&'|'&Product as DateCustProd_Key,
  Date as T2Date,
  Customer as T2Customer,
  Product as T2Product,
....

Create a link table using Load distinct..
LinkTable:
Load Distinct
 T1Date&'|'&Customer_Name&'|'&Product_Name as DateCustProd_Key,
  T1Date as Date,
   Customer_Name as Customer,
  Product_Name,
  Month(T1Date) as Month,
  Year(T1Date) as Year,
 'Table1' as FromTable
Resident Table1;

Concatenate
load distinct
  T2Date&'|'&T2Customer&'|'&T2Product as DateCustProd_Key,
  T2Date as Date,
  T2Customer as Customer,
  T2Product as Product,
  Month(T2Date) as Month,
  Year(T2Date) as Year,
  'Table2' as FromTable
Resident Table2;

[Russ]

Thanks Lisa! That did it...