Let's say I have the following table parts (of two much larger tables)...
CreditTable:
| Project | Contract Date | Value | Line | Position |
| 396 | 2/17/21 | 54 | 52 | 0 |
| 396 | 3/15/21 | 78 | 44 | 1 |
| 396 | 3/15/21 | 78 | 44 | 3 |
| ... | ... | ... | ... | ... |
OrderTable:
Project | Contract Date | Value | Line | Position |
| 396 | 2/17/21 | 54 | 30 | 4 |
| 396 | 3/15/21 | 78 | 22 | 1 |
| 396 | 3/15/21 | 78 | 22 | 2 |
| ... | ... | ... | ... | ... |
I need to match a credit to an order by Project, Contract Date, and Value. I need it to be a one-to-one matching. When I run my current script...
JoinedTable:
LOAD DISTINCT
Project&ContractDate&Value AS CreditKey
Project&'-'&Line&'-'&Position AS OrderNumber
RESIDENT OrderTable;
LEFT JOIN LOAD
Project&ContractDate&Value AS CreditKey
Project&'-'&Line&'-'&Position AS CreditNumber
RESIDENT CreditTable;
I get something like this...
| CreditKey | OrderNumber | CreditNumber |
| 39602172154 | 396-30-4 | 396-52-0 |
| 39603152178 | 396-22-1 | 396-44-1 |
| 39603152178 | 396-22-2 | 396-44-1 |
But I WANT this...
| CreditKey | OrderNumber | CreditNumber |
| 39602172154 | 396-30-4 | 396-52-0 |
| 39603152178 | 396-22-1 | 396-44-1 |
| 39603152178 | 396-22-2 | 396-44-3 |
Is there a way to do this kind of one-to-one mapping?
