Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Formula

Hussein982
8 - Asteroid

Hello All: 

 

I have an inventory file with more than 35k line items. the two key columns in this file are the item number and sub-inventory column. 

 

Its OK to have an duplicate item number under the condition that the sub-inventory should be different (attached highlighted in green for your reference).

 

What is NOT OK is to find an duplicate item number with the SAME sub-inventory. (attached highlighted in red for your reference).

 

The goal of this analysis is to find all duplicates item numbers with the SAME sub-inventory.

 

Any help would be much appreciated. 

4 REPLIES 4
Luke_C
17 - Castor

You could use a unique tool and select the two key fields. Anything that drops out into the D anchor would indicate a duplicate. You could join this back to your dataset to get the full population of duplicates to review.

gawa
15 - Aurora
15 - Aurora

Hi,

 

It might be wrong, but I understand that you try to find the unique data. Is it correct?

 

If yes, I recommend to use Unique tool. Please check which filed must be unique, then unique record will appear on U-anchor. At the same time, duplicated record on D-anchor.

If you just have to know only unique record, you can also use Summarize tool.

If you want to maintain duplicated data and maintain original sort along with identifying unique record, you can use Tile tool(tile method:"Unique Value" & check "Leave Unsorted")

 

If none of the above does not meet your demand, please let me know more about your query. Thanks.

Hussein982
8 - Asteroid

Thank you for your response. I appreciate it. However, I am NOT looking for unique. 

 

I am actually looking to find the duplicates (same item number and same sub-inventory). I have highlighted in red what consider abnormal and this is what I am looking for (see attached excel file for your reference). 

 

Appreciate your help in advance if you could me with a workflow to find all these duplicates (Same item number with the same sub-inventory)

gawa
15 - Aurora
15 - Aurora

Understood. You don't want to filter out unique/duplicate record, but want to judge it's normal/abnormal on each record.

How about this?

First, count the no. of unique record by Summarize tool(if 1, it's not duplicated. If 2 or more, it's duplicated)

Join with original record, and add a new field by Formula which assign OK or NG depending on no. of count.

In order to maintain the original order, I put record ID at upstream, and sort by record ID at end.

 

gawa_0-1633139847267.png

 

Labels