Remove duplicate records based on two columns

Hi All,

I have a set of records and would like to remove duplicate values based on two columns.

Document No	Invoice No
123	1
456	1
123	2
456	2

The correct set of records to be returned should be:

Document No	Invoice No
123	1
456	2

In other words, the invoice number cannot be repeated. If Invoice Number 1 has already matched to Document No 123, then it should not be matched again to Document No 456.

How can I do this in Alteryx? Using the unique tool either on both columns or one of the columns does not work.

Datasets

Join

Accepted answers

JosephSerpis

Hi @timothyyeo mocked something up that produces the output. Would need to be tested against you data to ensure it would work.

Filter_Two_Columns.yxmd

JoeS

Hi @timothyyeo

It looks like you may be opening the macro and running it as a workflow.

An iterative macro is designed to be used as a tool itself.

I have attached a workflow that uses the macro within it.

This then achieves the output that you wish.

There are lots of different resources available on the community about macros. I'd recommend starting with these:

this first

then this

MainWorkflow.yxmd

All comments

JoeS

Ok, now with the actual solution.

An Iterative Macro.

Whereby we loop round choosing the minimum Invoice No each time, then removing that invoice number and document number from the next iteration.

IterateInvoice.yxmc

MichalM

@timothyyeo

Is it always going to be the case that you will have a matching number of unique Document and Invoice numbers? If that's the case you'd probably want to do something like this

Group by Document No
Group by Invoice No
Join by record position

If that's not the case, what Invoice no will you use?

JosephSerpis

Hi @timothyyeo mocked something up that produces the output. Would need to be tested against you data to ensure it would work.

Filter_Two_Columns.yxmd

timothyyeo

Hi @MichalM,

Thanks for the fast reply. However, the outcome I need is:

Doc No	Inv No
123	1
456	2

However, the outcome of your method is returning

Doc No	Inv No
123	1
456	1

Effectively, I feel the most complete solution would be to create some sort of For or While loop in alteryx, to identify that if an Invoice No is already tagged to a Doc No, it should not be tagged to another different Doc No.

timothyyeo

Hi @MichalM ,

Yes, good question and I should clarify. The dataset I'm working with may not have a matching set of unique DocNos and InvNos. In those situations, the first match will be taken as correct.

DocNo	InvNo
123	1
456	1

For the above table, (123, 1) will be taken as correct. Ideally, the second record (456, 1) is to be flagged in separate table for further investigation.

DocNo	InvNo
123	1
456	1
123	2
456	2
123	3
456	3

For this table above, records (123, 1) and (456, 2) will be taken as correct.

(123, 3) and (456, 3) are to be flagged separately for investigation.

(456, 1) and (123, 2) are duplicates and simply discarded since both DocNos and InvNos are already matched correctly.

timothyyeo

Hi @JoeS ,

I tried to run your flow with a tweak sample dataset:

But the output does not seem correct (screenshots of the results of O Output and I Output below).

The correct output should be (123, 1), (456, 2), and (789, 3). As its my first time seeing an iterative macro in practice, is there anything additional I should do to run this workflow correctly?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703