Creating a workflow which loops until condition is met

Hi all,

Firstly wanted to say I'm loving using Alteryx so far! Secondly, wanted to apologise in advance for misnaming anything - I'll try my best to explain what I'm trying to do!

Simplified aim: I'm trying to get to a data set of competitor restaurants which are within a given proximity of a particular brand. e.g. Find the set of restaurants near all the McDonald''s in the UK.

Now, to get the data I want it so that I limit the share of restaurants in the competitor set, so one brand doesn't exceed 50% e.g. if there are 5 fast food restaurants in the comp set, and 3 are Burger King, then I'd want to adjust the data.

So far I've tried this manually, and it works, however, as I'm a massive data geek, I want to know if I can create a loop which can do this, without me creating multiple sets.

I have the data ranked by proximity at the moment, so I'm only picking the nearest 7 restaurants to a given McDonald's. Then I have tested if any brand accounts for over 50% of the competitor set. My manual flow says... if it has more than 50% of one brand, add the next nearest restaurant than isn't the brand that has the highest share of compset i.e. if there are 60% Burger King in the set, add the nearest brand that isn't Burger King. I've done this latter part manually, but repeating the flow a few times i.e. at the 7th closest restaurnt... test... add the 8th.... test.

Is there a better way to do this which says..... Test if comp set >50%, if so add the next closest until that condition is met. Is there a tool in the palette I've overlooked, I suspect I've overcomplicated this! Some of the similar posts in here talk of macros, but their problems are more complex than mine.

Thanks in advance to anyone that can help!

Spatial Analysis

Accepted answers

tcroberts

It sounds like you'll want an iterative macro. If you haven't tried working with Macros in Alteryx so far, there's a great tutorial by @PaulT here

If you could provide a mock dataset or a sample workflow I could try to whip something together to give you an idea of what you'd want.

In general though, you'll want something that would test that condition, and then, if the share is >50%, removes that closest restaurant (the one that caused it to be >50% share) from the data stream that feeds into the iteration Output Tool.

Then, on the next run, your potential restaurants would be everything you had looked at previously, except the one that leads it to be >50%.

Note that the macro will keep iterating until it reaches the specified max iterations, or until the iteration output stream does not receive any more records, and so you'll likely want to add a second test which removes everything once your 'selected' restaurants hits the desired size.

Let me know if you have any questions,

Cheers!

All comments

tcroberts

It sounds like you'll want an iterative macro. If you haven't tried working with Macros in Alteryx so far, there's a great tutorial by @PaulT here

If you could provide a mock dataset or a sample workflow I could try to whip something together to give you an idea of what you'd want.

Then, on the next run, your potential restaurants would be everything you had looked at previously, except the one that leads it to be >50%.

Let me know if you have any questions,

Cheers!

Austin09

Wow, thanks! That sounds right, the only change i'd make, is I'd prefer to add additional restaurants to the data set to satisfy the 50% condition, as I'd like a minimum of 6 in the comp set.

I'll take a look at that tutorial now.

My workflow is a bit of a mess as I've been trying to solve via trial and error. I'll tidy it up after watching the tutorial and upload it so you can see.

Thanks again for your help!!!

Austin09

Thanks again, that is exactly what I was looking for. Really pleased there is a solution for this!

I've attached part of my workflow - sorry it is a mess. This is a similar workflow to the restaurant one I've been working on, with the same principal, just using hotels instead of restaurants. The added complication is that I'm using quantity of rooms rather than # of hotels i.e. needs to be fewer than 50% total rooms. I've just selected the bit that is more relevant, the rest is just to get me to this point and join various data sources. Let me know if you need more steps or walking through my messy workings.

This sums the total properties in a data set - a data set being a brand in a city i.e. brand A in London. Then calculates the share of all competitor brands in that city.

For those that are over 50%, I've created a separate flow, which is what I've attached. The properties are ranked by proximity currently, so I planned to add to the data set the next nearest property and retest. To pick the nearest competitor, I'd like to use a formula like 'min' to pick the closest.

e.g. like my restaurant example - the data set would be London data for McDonald's. In London there are for example 100 McDonald's which would all be included, each with 7 competitors. I'd want to pick the nearest competitor to any of those 100 McDonald's - not sure if my ramblings made sense there.

Let me know if you want me to screenshare or something and explain.

Example loop.yxmd

Austin09

Thanks again, that is exactly what I was looking for. Really pleased there is a solution for this!

This sums the total properties in a data set - a data set being a brand in a city i.e. brand A in London. Then calculates the share of all competitor brands in that city.

Let me know if you want me to screenshare or something and explain.

Example loop.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703