We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Is there a good way to find fields that do not match when using the unique tool?

RobMotiwalla
7 - Meteor

I am using the Unique and Only Unique tools to try and find matches on a number of different fields. I want to make sure it pulls all of those matching rows, but I want to also have a criteria where a specific field does NOT match. Is there an easy way to do this?

 

Thanks!

7 REPLIES 7
gabrielvilella
14 - Magnetar

If you could please provide a sample dataset and the desired output would be helpful. 

RobMotiwalla
7 - Meteor

Hi @gabrielvilella 

 

Here is an example of the data. I am trying to match on the account, doc date, and absamount (in yellow), but I would like for the doc type and/or amount (red) to be different. From the data below, the only ones that i would like to see are ones in green.

 

Thanks for the help!

 

ReferenceAccountBranch NameProfit CenterBottlerAccounting Doc #Doc TypeDoc DatePosting DateAmountAbsAmountInvoice Reference
9.1E+13300000385  UNKNOWN1400152916DZ10/22/201810/19/2018-1010 
9.1E+13300000385  UNKNOWN1400152916DZ10/22/201810/19/2018-1010 
9.1E+13300000385  UNKNOWN1400152916DZ10/22/201810/19/2018-1010 
9.1E+13300000385  UNKNOWN1400152916DZ10/22/201810/19/2018-1010 
9.1E+13300000385  UNKNOWN1400152916DZ10/22/201810/19/2018-1010 
2419808AC871300000141300096550CI00750005Viking - Duluth1506119365CZ9/2/202110/13/2021-1010V
1376617C123300000141300063911CI01600000Love Bottling Co.1506218296CZ1/26/20221/29/2022-1010V
1102102C17486300000141300005042CI02150000Binks CCBC1506213298CZ1/20/20221/26/2022-1010V
2123188C17486300000141300005042CI02150000Binks CCBC1505501691CZ3/12/20203/18/2020-1010V
302876C896300000141300161656CI03400000Hancock Bottling Co.1505858465CZ2/2/20212/6/2021-1010V
304728C896300000141300161656CI03400000Hancock Bottling Co.1506010682CZ6/22/20216/25/2021-1010V
590671300000149  UNKNOWN1400164740DZ4/7/20214/7/2021-1010 
1166948300000149300190382CI00100005Consolidated CONA1400293933DZ8/20/20212/24/2022-10101838822224
825395300000149300133358CI00150005United CONA1400137535CZ8/20/20218/20/202110101837368389
1505899146300000149300111051CI00450005NNE CONA1505899146CZ3/9/20213/18/2021-1010V
RobMotiwalla
7 - Meteor

Hi @gabrielvilella 

I just replied to your comment and posted it, but I do not see the comment now. So my apologies if this is duplicated. I would like to find duplicates on the account, doc date, and absamount (yellow). But I need the doc type and/or amount to be different (red). From the data below, following those criteria, the only rows that I would expect to see are highlighted in green.

RobMotiwalla_0-1646252226150.png

 

Thank you for the help!

gabrielvilella
14 - Magnetar

@RobMotiwalla thank you for the explanation! You need to insert a Summarize tool, group by Account, Doc Date and AbsAmount, and count distinct Doc Type and Amount. All records with count > 1 are what you call duplicates. Then you can use a join to filter those records. If you provide the file or just paste the table here I'd be able to build a sample workflow, it can be just some sample data. 

RobMotiwalla
7 - Meteor

I tried pasting in straight from Excel before and it gave me an html error. Let me try again.

 

AccountBranch NameProfit CenterBottlerAccounting Doc #Doc TypeDoc DatePosting DateAmountAbsAmount
300000385  UNKNOWN1400152916DZ10/22/201810/19/2018-1010
300000385  UNKNOWN1400152916DZ10/22/201810/19/2018-1010
300000385  UNKNOWN1400152916DZ10/22/201810/19/2018-1010
300000385  UNKNOWN1400152916DZ10/22/201810/19/2018-1010
300000385  UNKNOWN1400152916DZ10/22/201810/19/2018-1010
300000149300190382CI00100005Consolidated CONA1400293933DZ8/20/20212/24/2022-1010
300000149300133358CI00150005United CONA1400137535CZ8/20/20218/20/20211010
300000141300096550CI00750005Viking - Duluth1506119365CZ9/2/202110/13/2021-1010
300000141300063911CI01600000Love Bottling Co.1506218296CZ1/26/20221/29/2022-1010

 

gabrielvilella
14 - Magnetar

Here is an example.

RobMotiwalla
7 - Meteor

Thanks @gabrielvilella, that is exactly what I was looking for!

Labels