Challenge #154: Permute the Data

Last week's solution can be found here.

This week's solution is working to permute some data! This challenge is derived from an question asked by @iellingsonbg, and was solved by the illustrious @MarqueeCrew. @MarqueeCrew then messaged us letting us know a great challenge was available that we should tackle! If you ever have a great challenge of your own or see another problem around the Community worth tackling, let us know!

As for the challenge itself, we are presented the options (tools). What we would like to do is determine all the combinations these tools can exist in an 'on/off' state. For three options we can see the following combinations. Note that the end result data is not formatted this way, but simple formatted here for ease of understanding.

Browse	Input	Output
Off	Off	Off
Off	Off	On
Off	On	Off
Off	On	On
On	Off	Off
On	Off	On
On	On	Off
On	On	On

If you want to take a more difficult route, try to setup your workflow where it would be dynamic to handle more than three tool options and more toggle options than on/off!

challenge_154_start_file.yxmd

Parse

Macros

Join

Preparation

Advanced

Data Analysis

Data Preparation

Difficult

Accepted answers

All comments

MarqueeCrew

@JoeM,

Illustrious is very kind of you. I'll defer posting a solution until the real challenge guru's come up with some interesting alternatives.

Cheers,

Mark

patrick_digan

I had a little bit of a headstart as I tried to solve @iellingsonbg's original problem. Thanks @MarqueeCrew for putting this as a weekly challenge!

Spoiler

I've successfully overcomplicated another weekly challenge. I turned this into a pure math problem. For the original problem, I set this up where 0 = Off and 1 = On.

Browse	Input	Output
0	0	0
0	0	1
0	1	0
0	1	1
1	0	0
1	0	1
1	1	0
1	1	1

So there's 8 rows of data since 2 toggles (ON/OFF) to the power of 3 items (Browse/Input/Output) = 8. Then I realized that if I combined all the columns into 1 column, this is basically counting up in binary (base 2) with extra zeros padded to the left. Row 1 = 000(binary)= 0(decimal), Row 2 = 001(binary)=1(decimal), Row 3 =010(binary)=2(decimal). So now my table looks like this:

Browse	Input	Output	Binary	Decimal
0	0	0	000	0
0	0	1	001	1
0	1	0	010	2
0	1	1	011	3
1	0	0	100	4
1	0	1	101	5
1	1	0	110	6
1	1	1	111	7

So to solve the problem, I generated my 8 rows of data with a simple recordID. Then I converted the recordID from Decimal to Binary. I created an iterative macro to do the conversion.

Note that my macro will scale to any number of tools and toggles. You basically just need to convert the recordID from Decimal (base 10) to base x (where x= number of toggles) which is what it's doing.

challenge_154_start_filev2.yxzp

MarqueeCrew

@patrick_digan,

How horizontal of you! Have you switched axis? I haven't noticed before.

Cheers,

Mark

Ladarthure

Nice one, did a brute force way, but trying to find out a macro solution

challenge_154_start_file.yxmd

danilang

High School Math to the Rescue!

Thanks for the challenge @MarqueeCrew

Spoiler

Scalable solution that handles varying number of tools! Not varying toggle options though! That will have to wait for a generic base-N macro.

Edit: @patrick_digan IntToBin() does most of the heavy lifting. But then again you have the Base-N macro that I still have to work on! DOH!

Dan

challenge_154_solution_DL.yxmd

karp

Here's my solution. It's dynamic in the sense that the workflow will adjust itself based on the quantity of tools being input...in theory. For full dynamic ability, I would have to make a dynamic append macro. Interesting challenge, but I'm already crunched for time today Actually, that was my starting thought. Upon closer review, I summarize to get max records but ended up not using it. The intent was to take that value for a dynamic append macro.

All that being said, my solution is still a $5 answer to a $100 question. It works due to "On " (there's a blank in the quotes) and "Off" strings being 3 characters each. Laziness traded for expediency I suppose! (insert Bill Gates quote here)

Screenshot:

Spoiler

challenge_154_karp_solution.yxzp

kat

Not what you'd call elegant, but for today, this'll have to do

Spoiler

Challenge #154.yxmd

papalow

It is not a thing of beauty....

challenge_154_solution.yxmd

T_Willins

Made me remember my high school math. Complex, but dynamic solution. Should allow for any number of Option 1 or Option 2 variables. Likely could make this much simpler, but real work using Alteryx took my time today (Data mining 126 Excel workbooks - no problem).

Spoiler

challenge_154_solution.yxmd

estherb47

Totally brute force here, and I want to create a more dynamic solution that can handle multiple toggles as well as multiple inputs. But it's a start.

Spoiler

challenge_154_EHB_solution.yxmd

JoshuaGostick

Not very dynamic as you'll need to add an extra Append tool for each extra tool options. However, it's a relatively straightforward workflow and it works for this challenge!

Spoiler

challenge_154_JG.yxmd

Wouter

That was fun! Here's my solution:

Spoiler

challenge_154_solution_WK.yxmd

RolandSchubert

My solution - dynamic only regarding number of tools.

challenge_154_solution_rsc.yxmd

MarqueeCrew

Here is my dynamic solution in a picture:

Spoiler

The way it works (dynamic):

Spoiler

I compute the number of permutations and generate the number of rows necessary to create the needed on/off (or other choices) states. By combining the options into a single string I found it a simple way to dynamically handle the options and states.

This workflow helped the user and was a fun challenge for @patrick_digan and @patrick_mcauliffe to chat about offline. Even @JoeM thought that this was just a simple use of an append fields tool (before he dug into it). But Joe's a nice guy and he put the dynamic nature of the workflow as an extra credit option. No brute force here @estherb47.

Cheers,

Mark

challenge_154_MarqueeCrew.yxmd

estherb47

Excellent, @MarqueeCrew ! Where I'm struggling though is how can you make it handle more than two toggles (on/off, e.g.) IntToBin is wonderful for 2 choices, but how can we make this work for 3, 4, 5 different answers for each option?

cplewis90

This is definitely a brute force method as I am still working on the macro to be more dynamic. Definitely curious to see if anyone can be dynamic without using a macro!

Spoiler

challenge_154_CL.yxmd

karp

@MarqueeCrew, I really like the simplicity of your solution in the sense that it's a clean way to do the math without brute-forcing it with nested append tools like I did. I'm curious to know why you wrote your formula the way you did in Formula Tool (50). Couldn't you just leave off the "-1"? It looks like you account for it in your Generate Rows (40) by having your initialization expression at "0". I'm wondering if there's a reason you did it this way. Also, the Concat Options is clever. I think if I had thought of that from the beginning of my solution, things would have turned out differently - I seem to remember banging my head against the wall yesterday as I have all the results themselves concatenated as "Off,On,Off" (etc.), but was stumped at to how to break out the actual tool labels. Definitely one of those "so obvious, why didn't I think of that". Good experience to implement in future workflows, thanks!

lminors

Don't need maths - leave it up to chance!

Spoiler

Generate shed loads of rows and assign either 1 or 0 (On or Off) at random. Concatenate these and take all unique options. Voila!

challenge_154_LMsolution.yxmd

ponraj

Here is my workflow for this challenge!

Spoiler

challenge_154_solution.yxmd

pasccout

Wow... nice challenge this week... Had to really think about it...

The solution is dynamic... you can change either option 1 or option 2 input and it will assign them correctly...

The final results works... not the same order as yours though...

challenge_154_my_solution.yxmd

RichoBsJ

Hi! Here my solution

challenge_154_richobsj.yxmd

catarina_pimpao

Spoiler

AWC_154.yxmd

ai_zahran

My solution for the challenge, although I admit I feel it is overshooting the problem.

challenge_154_solution.yxmd

etzele

Here is my solution. It works with different toggle options but not with different tool options (yet). Maybe it works when I add an interative macro.

Spoiler

solution.yxmd

Sebenius

a messy solution this week. with a bad base X macro that will create more problem then when it solve but wanted to get somethign in before the weekend.

Spoiler

Workflow

BaseX Macro

challenge_154_Done.yxzp

GiuseppeC

Loved it! Brought me back to ...

Spoiler

binary representations!

Here's my dynamic solution:

GCchallenge_154.yxmd

JamesMA

Tough challenge. I have a solution although it is not very dynamic.

Spoiler

James Marks Week 154.yxmd

ggruccio

Fun excercise @MarqueeCrew !

challenge_154_gg_finish.yxmd

kelly_gilbert

This one was deceptively challenging! At first, I questioned the Advanced tag, but realized there was more to it than I anticipated. It's interesting to see the different ways everyone has solved this.

Spoiler

* Use the Generate Rows tool to create a row per scenario ([Tools]^[States] scenarios)
* Create a row for each scenario/tool
* Use a formula to identify the state for each row

challenge_154_solution_klg.yxmd

NicoleJohnson

My solution! I chose to create an app that would allow a user to enter their own choices for tools and their states. I then used an iterative macro to create the combinations for any number of tools/states.

Spoiler

Originally, I tried to do a batch macro within an iterative macro - seemed like it should work, but couldn't get it to iterate, so I went with a different approach for just an iterative macro instead. Key tools in both the workflow and the iterative macro are Transpose & Cross-Tab, Multi-Row Formula, and the Append tool.

Cheers!

challenge_154_NicoleJohnson.yxzp

BenMoss

I can't take much credit for this one; 6 months ago I was working on a client project with a very similar challenge, assessing fuelling options on a route (you either stop or you don't), with the objective to reduce total fuel cost by stopping at the cheapest locations.

@PhilipMannering stepped in and built the permutations solution for me which I've adopted here so it can be shared (Y)

Spoiler

challenge_154_BM.yxmd

TylerNa

I was able to solve it but it's not a dynamic or elegant solution. Thanks for a tough challenge!

Spoiler

WC 154 OnOff.yxmd

SaintVincent

Thanks for the Challenge, stray to the point 8 tools no macro

challenge_154_start_file (1).yxmd

JosephSerpis

Challenge Completed

challenge_154_Joe_Serpis.yxmd

ahmadtookhy

Solution

challenge_154_Toukhy_Solution.yxmd

ahmadtookhy

been struggling with making it dynamic, not to insert a single character/parameter that corresponds to the data or the count of rows in both inputs.

My solution attached

challenge_154_Toukhy_Solution_v2.yxmd

sam_seed

Hey everyone. Here's my dynamic solution (any number of options, and any number of states that those options can take), done without creating a macro.

Thanks for a fun challenge!

Sam

challenge_154_start_file (SS solution).yxmd

Kashush

Kat, My workflow is similar to yours.

challenge_154_Solution_20190324.yxmd

SeanAdams

the joys of boolean logic! Does everyone smile when they get to do bit-wise logic?

Spoiler

Set up an iterator for each state that's possible. This kid of idea works for any system that can be reduced to a numeric system

challenge_154_SeanAdams_Solution.yxmd

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167