2 Replies Latest reply: May 15, 2018 12:30 PM by Mohamed Elafifi RSS

    Link Tables without "joining" them

    Mohamed Elafifi

      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 ShippedCustomerQTY Shipped
      1/10/2018124
      1/11/2018212
      1/12/201834
      1/13/201841

      and Non-Conformance Table

      NC DateCustomer
      1/11/20182
      1/13/20181
      1/14/20183
      1/17/20184

      Expected out, total:

      Year/MonthQTY ShippedNC Count
      01/2018414

      and if I filter by a given customer, customer 1 for example:

      Year/MonthQTY ShippedNC Count
      01/2018241

       

       

      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