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

Selecting groups of three

jpoferreira
5 - Atom

I have a dataset that looks like this:

 

Loc 1Loc 2Loc 3Value
abc1000
def900
agh700
ghf600
ogh500
iij400
ijo300

 

Imagine that I have teams and I need to assign places for them to go. Each team can go to a max of 3 locations and two teams can't be assigned to the same location.

 

So I want to find the combinations that are unique in its three locations

 

The rows are combinations of locations that already match the criteria required.

 

The value is just because I want to select the ones with the highest value ,so I sorted the combinations of three by value

 

In this example, the result would be:

Loc 1Loc 2Loc 3Value
abc1000
def900
ogh500
iij400

 

See that combinations may have only two locations (i,i,j)

 

So basically, for every row I need to look at the rows above and see if any of the locations in the current row is in any of the locations in the rows above. If it is, delete the row, if not keep it, then go to the next row. I am trying to use batch macro but I am new to it.

Can someone help me out?

2 REPLIES 2
andyuttley
11 - Bolide
11 - Bolide

Hi  @jpoferreira

 

Yes, you can do that with a macro. I've dropped a very quick working example in the attachment below (using your example file). The workflow appends the field [KeepRow] (with a 1 or 0)  so you can then see which rows you're removing at the end. The macro essentially searches for repeat value in any of the rows above (by transposing). I've done it as an iterative macro as we don't then want to compare to any rows above that we have since deleted (i.e. since changed to 0). 

 

Let me know if there's any issues or it's not what you were after. Feel free to tidy the macro up; I'm sure there are a couple of ways to do this! 

 

Thanks,

 

Andy 

jpoferreira
5 - Atom

Perfect!!

Thanks a lot!

Labels