1 Reply Latest reply: Jan 3, 2015 8:16 AM by Colin Albert RSS

    Best way for Linking tables

      Hi,

      I have a model which includes 2 tables: Orders, and invoices.

      Not all invoices would reference to an order, and not all orders would have an invoice.

      The orders have the following fields:

      OrdID

      OrdDate

      CustID

      ItemID

      Qty

      The invoices table has the following fields:

      InvID

      InvDate

      OrdID

      ItemID

      Qty

      What would be the best way to link between the 2 tables so an item analysis can be performed for both?

      Thanks in Advance!

        • Re: Best way for Linking tables
          Colin Albert

          I would concatenate the order and invoice data to a single table.

           

          Is the Order Qty and Invoice Qty the same for a matching OrderId & ItemId? If yes, then you only need on Qty field, if not then you will need to rename the fields as OrderQty and InvQty.

           

           

          Try something like

           

          Orders:

          load

               OrdID,
               OrdDate,
               CustID,
               ItemID,
               Qty as OrderQty

          from <Your Order Source> ;

           

          concatenate(Orders)

          load

               InvID,

               InvDate ,
               OrdID,
               ItemID,
               Qty  as InvQty

          from <Your Order Source> ;