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!
Solved! Go to 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.
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!