Dynamically Create Columns in an Analytic App

[rnewby]

I've been working for a few months on this problem, so hopefully, someone can help now...

I have a decent-sized dataset (~200 columns, ~250k rows), and inside there are a handful of columns with company names. I need to search through those columns and create a new binary column based on whether a certain company is there or not. The challenge is that this is part of an app, and the companies should be chosen based on the user. So UserA wants to see Company1, 4, and 6, while UserB wants to see Compant 2, 5, and 6. I won't know the companies in advance, nor how many companies will be chosen, so I can't create the columns in advance. 

I can't share the data, but I made this small dataset as an example:

Animal1	Animal2	Animal3	Animal4	Animal5	Zoo	Budget(k)	Director
Tiger	Monkey	Lion	Ostrich	Penguin	Washington	8000	Mr. Brown
Monkey	Zebra	Giraffe	Lion	Gorilla	Pittsburgh	3500	Ms. White
Tiger	Lion	Llama	Penguin	Giraffe	Oakland	10000	Ms. Green
Cheetah	Monkey	Gorilla	Snake	Eagle	Cincinnati	1500	Mr. Black
Lion	Gorilla	Giraffe	Antelope	Bear	Kansas City	4200	Mr. Orange
Eagle	Llama	Tiger	Bear	Snake	Phoenix	5300	Ms. Red
Llama	Ostrich	Warthog	Penguin	Cheetah	Los Angeles	8500	Mr. Blue
Warthog	Penguin	Tiger	Giraffe	Lion	New York	9700	Ms. Pink
Gorilla	Tiger	Antelope	Cheetah	Warthog	Boston	8800	Mr. Yellow
Cheetah	Elephant	Llama	Penguin	Tiger	Miami	2500	Mr. Gray
Penguin	Lion	Bear	Eagle	Gorilla	Las Vegas	2100	Ms. Peach
Giraffe	Elephant	Warthog	Antelope	Bear	Seattle	4600	Ms. Purple
Lion	Penguin	Gorilla	Llama	Eagle	Detroit	1900	Ms. Silver
Tiger	Llama	Warthog	Bear	Cheetah	Atlanta	7000	Mr. Gold
Snake	Lion	Llama	Giraffe	Zebra	Nashville	2000	Mr. Maroon

In this scenario, I have a list box that populates all the animals available:

Antelope
Bear
Cheetah
Eagle
Elephant
Giraffe
Gorilla
Lion
Llama
Monkey
Ostrich
Penguin
Snake
Tiger
Warthog
Zebra

The User of the app would choose whichever animals are desired. For example: Giraffe, Warthog, Bear

New columns would be generated, like:

HasGiraffe	HasWarthog	HasBear	Animal1	Animal2	Animal3	Animal4	Animal5	Zoo	Budget(k)	Director
0	0	0	Tiger	Monkey	Lion	Ostrich	Penguin	Washington	8000	Mr. Brown
1	0	0	Monkey	Zebra	Giraffe	Lion	Gorilla	Pittsburgh	3500	Ms. White
1	0	0	Tiger	Lion	Llama	Penguin	Giraffe	Oakland	10000	Ms. Green
0	0	0	Cheetah	Monkey	Gorilla	Snake	Eagle	Cincinnati	1500	Mr. Black
1	0	1	Lion	Gorilla	Giraffe	Antelope	Bear	Kansas City	4200	Mr. Orange
0	0	1	Eagle	Llama	Tiger	Bear	Snake	Phoenix	5300	Ms. Red
0	1	0	Llama	Ostrich	Warthog	Penguin	Cheetah	Los Angeles	8500	Mr. Blue
1	1	0	Warthog	Penguin	Tiger	Giraffe	Lion	New York	9700	Ms. Pink
0	1	0	Gorilla	Tiger	Antelope	Cheetah	Warthog	Boston	8800	Mr. Yellow
0	0	0	Cheetah	Elephant	Llama	Penguin	Tiger	Miami	2500	Mr. Gray
0	0	1	Penguin	Lion	Bear	Eagle	Gorilla	Las Vegas	2100	Ms. Peach
1	1	1	Giraffe	Elephant	Warthog	Antelope	Bear	Seattle	4600	Ms. Purple
0	0	0	Lion	Penguin	Gorilla	Llama	Eagle	Detroit	1900	Ms. Silver
0	1	1	Tiger	Llama	Warthog	Bear	Cheetah	Atlanta	7000	Mr. Gold
1	0	0	Snake	Lion	Llama	Giraffe	Zebra	Nashville	2000	Mr. Maroon

Is there any way to do this dynamically in Alteryx?

Thanks!

[CharlieS · Accepted solution]

Thanks for the example data to work with. I've attached an example solution. Check it out and let me know if this works for you. 

The key to achieve the desired output here is to Transpose the data so the fields used to perform the matching are standardized. After that, it's just a matter of transforming the data back using a Cross Tab tool and joining it back to the original data stream using RecordIDs.

[rnewby]

Yes! That worked wonderfully. Thank you so much. 

It took a little finessing to fit into my existing app, but once there it worked perfectly!