community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to select distinct records

Atom

I have a dataset like below and each one is a record. If I have already found a match for 730 ID I should not further use it and match 731 only to 358

 

The only way I can think (but expect there is an easier solution) is to use a multi-row formula keeping a track of what has been used in each column.

 

It does this by making a string containing all used ids. If the pair is unused then it will record that they are used. 

Finally, a second run through to just keep the rows where the values changed.

 

I added a few more pairs into the sample you provided. Hopefully this is the kind of thing you meant.

Highlighted
Bolide

This doesn't work, but maybe it's on the right track to inspire the solution.

 

Because you are looking for the first unique import record ID you can just sort by record ID and take the first unique ID in the stack.

I've attached an example of the flow. (Please note I have modified your example data to include more Import Record ID's)

 

Flow:

Unique Records Flow.PNG

 

Result:

Unique Records Result.PNG

 

Mark First Unique Record ID:

Mark First Unique Import Record ID.PNG

I disagree my solution does provide how I understand the correct result to be:

2017-10-26_09-00-02.jpg

 

Having used an Import_RecordID or an Export_RecordID neither can then be used going forward.

 

Thinking about it an iterative is a cleaner approach. Have attached a simple.

Bolide
I agree with your approach. An iterative macro is cleaner. My thought process with trying a sort is instead of passing down every combo that ever happened, all that needs to be passed down Is the last record that matched so that it can be skipped. By bedtime, however, an iterative macro seemed like the only solution as well. (Essentially making a while loop)
Labels