Avoid duplica assing of invoice numbers to transaction report.

[Dprado006]

Hi Community, this is my first post, I hope you can help me.

I have a transaction report with suppliers of X month. I also have a file with the total of invoices received in the year. What I want is to assing an invoice numer to each row of the transaction report, for this I created an unique ID in both files using Tax ID and the total amount of the transaction.  I have tried several solutions but I keep hitting a wall regarding duplicate ID as I can have several suppliers in that month for wich I have several invoice for the same amount, thus creating duplicate ID in both files. Now my ideal scenario would be for my transaction report to have a unique invoice number assigned, when encountering these duplicates that instead of assigning the first match would go over to the nex unasinged Invoice number. The complication is also that there is no fixed amount of duplicated for some suppliers I have 2 or 3 for others as much as 40. 

Attached is the dummy data. 

[jrlindem]

I can certainly appreciate that if you're matching your contrived unique_id that you're only going to get the first match, like an excel VLOOKUP.  But if you JOIN in alteryx and then union the streams back together you'll get all matches even if there are many as well as unmatched (if needed).

Or maybe i'm not understanding your ask correctly?

Can you also attach what your expected results would look like?  Even if you have to manually build it to illustrate?  -Jay

[jrlindem]

Took a deeper look at the data.  If you're trying to join these without the duplication and match up the right transaction to the right invoice, you're going to need another common field across both datasets to build a unique_key that differentiates.  Without this, there isn't a way to logically know which one is the right match.

[binu_acs]

@Dprado006 Maybe you could try the tile tool for your use-case, sample workflow attached