Hi All,
I have two tables and I am trying figure out how to link them without joining them. The problem with joining them is that each table has no distinct identifier to the other. For example, dates in the below tables will more than likely never match up (and they shouldn't). Also, if I join by customer from the data load, I will get repeated records, as the two tables combined would create an n:m relationship.
Essentially, what I would like to see is something that gives me total QTY Shipped for any given month as well as the count of Non-Conformance for that same given month, and if I want to get more granular, I should be able to filter on a customer and view the same data, only it should be reduced to just the customer. Below is an example
Orders/Ship Table
| Date Shipped | Customer | QTY Shipped |
|---|
| 1/10/2018 | 1 | 24 |
| 1/11/2018 | 2 | 12 |
| 1/12/2018 | 3 | 4 |
| 1/13/2018 | 4 | 1 |
and Non-Conformance Table
| NC Date | Customer |
|---|
| 1/11/2018 | 2 |
| 1/13/2018 | 1 |
| 1/14/2018 | 3 |
| 1/17/2018 | 4 |
Expected out, total:
| Year/Month | QTY Shipped | NC Count |
|---|
| 01/2018 | 41 | 4 |
and if I filter by a given customer, customer 1 for example:
| Year/Month | QTY Shipped | NC Count |
|---|
| 01/2018 | 24 | 1 |
Any help would be much appreciated. I've been working on a solution on and off for a couple of weeks, now, and I just haven't been able to figure this out.
Thanks in advance,
Mohamed
