Link tables together using Unique Key

[GreatGreekYogurt]

Hi All,

I created script to Load multiple sheets from one Excel file with 1 load statement. The number of rows loaded is correct, however when the next table is loaded (also number of rows correct) and is linked with key field the result comes incorrect - the rows are being duplicated 2 times. Can anyone please tell me what could be the issue here?

For each SheetName in 'Orders 2014','Orders 2015','Orders 2016','Orders 2017'

MainTable:
LOAD
    [Row ID],
    [Order ID],
    [Order Date],
    [Product ID],
    Subfield([Product ID],'-',1) as [Category Code],  
    Subfield([Product ID],'-',2) as [Sub-Category Code],
    [Product Name],
    [Sales],
    [Quantity],
    [Discount]  
FROM [lib://...]
(ooxml, embedded labels, table is [$(SheetName)]);

NEXT;

Supply:
LOAD
    [Order Number],
    [Product ID],
    [Supply Quantity],
    [Supply Price]
FROM [lib ...]
(ooxml, embedded labels, table is Supply);

So both tables are linked with Primary Key [Product ID]

So the [Quantity] that comes from main table corresponds to the correct [Order Date] (both fields from Main table) but is duplicated 2 times. And [Supply Quantity] that comes from Supply table - everything is wrong.

The end result should be 3 rows with [Order Date] showing only 1 row per date.

End result supposed to be:

[Row ID],[Product ID],[Order Date],[Quantity], [Supply Quantity]

5495, FUR-BOO-10000330, 11/28/2014, 4, 6

1761, FUR-BOO-10000330, 9/5/2015, 4, 5

6402, FUR-BOO-10000330, 5/30/2017,  2, 3

Can anyone please help me here.

[Lisa_P · Accepted solution]

I think that the Product ID is the wrong key for this situation.

What is the unique key here ?

[GreatGreekYogurt]

[Order Number] is the Unique Key in Supply table. It doesn't match with any field in Main table. How should I link them together?