Best way for Linking tables

[#1]

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!

[Colin-Albert · Accepted solution]

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> ;