General ledger & Detailed ledger combination, problem-2

Hi, When combining 2 ledgers with join tool by matching Transaction id and Account number. Because those 2 are the only matching columns in datasets. Join tool is creating some additional duplicates. Are there any other ways to handle the problem? See attached example file.

GL & DL example.xlsx

Datasets

Custom Tools

Best Practices

Data Investigation

Database Connection

Alteryx Editions

Accepted answers

Gaurav_Dhama_

There you go.

Here after getting the duplicate, we just needed to make sure that the correct amount against customer id is flowing.

GL DL second Example.yxmd

All comments

Deepanshi_024

Hi,

This is a great question and a very common scenario! The extra records are appearing because the Join tool creates a match for every possible combination of your join keys. If a key exists twice in the left file and once in the right, you'll get two records in the output.

The best practice is to remove these duplicates before the join. You can easily do this with the Unique tool.

Solution: Use the Unique Tool

Place a Unique tool onto the data stream for each of your ledgers before they connect to the Join tool.
In the configuration for each Unique tool, check the boxes for both Transaction id and Account number.
Connect the "U" (Unique) output anchor from each Unique tool to the "L" and "R" inputs of your Join tool.

This ensures only the first record for each unique combination is passed to the join, preventing any duplicates from being created.

If this post solves your problem, please help our community by marking it as an accepted solution. This will make it easier for others to find the answer!

AnarB

Thanks for your reply. I tried that before in order to decrease possible combinations but Unique tool itself is leaving essential rows to complete full result.

Gaurav_Dhama_

There you go.

Here after getting the duplicate, we just needed to make sure that the correct amount against customer id is flowing.

GL DL second Example.yxmd

Quick Links

Unanswered
Community Events
Groups

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280