Very simplified example: I have two tables that are linked by an ID. One table containing orders and the other invoices. I need to find a way to ascertain which orders are invoiced when but there is no key to link specific order with invoice date.
Any idea how I can create a key ? Please look at the enclosed for further information.
There is nothing in this model that will allow you to directly link from one "line" to another, if that is truly the model you are dealing with then you probably need to change your approach. I would normally expect to find an order line reference number in the order table and an equivalent line reference number in the invoice table, but that is not there.
In this case, you can probably only hope to achieve a SUM comparison of order vs invoice, so you could create a model that shows you the sum of the order value for ID 1 as $400, and next to that, show an invoice amount for ID 1 as $300 giving you a -$100 discrepancy between the two.
To do this you probably need to create two new tables in the load script that are doing a sum grouped by ID.
