Converting list of strings to dummy variables

naomirht

Hi Everyone, 

I'm new to Alteryx and would like to create dummy variables from a list of strings contained in one field.

How can I go from this table:

ID	Product_type
1	A_C_B
2	B_C
3	D
4	E_A
5	F

to this one:

ID	Prod_A	Prod_B	Prod_C	Prod_D	Prod_E	Prod_F
1	1	1	1	0	0	0
2	0	1	1	0	0	0
3	0	0	0	1	0	0
4	1	0	0	0	1	0
5	0	0	0	0	0	1

Given my basic knowledge of Alteryx, I would really appreciate a solution without applications.

Thank you in advance for your assistance,

MarqueeCrew

@naomirht,

Here's my thought process.  You want to parse the product type data with the underscore.  Now you don't know how many types that you will find.  That happens to be a good thing.  You can parse the data to ROWS.  Now each set of ID + Type gets it's own record.  For each record that does exist, we will create a new field "Value" and set it to 1.  We're vertical and have lots of 1's.  Now go HORIZONTAL and create a header record for each type that exists.  Using the cross tab  tool, you'll create all product types as HEADERs and all known 1's as values.  Where a product doesn't exist for a given ID, it is Null.  Now you can use a multi-field formula to turn all nulls to 0's.

Sounds easy enough....

Guess what?  It worked!

If you want to add "Product_" as a prefix to the field names, then in the formula you can modify the "NAME" value with:

"Product_"+[Name]

Cheers,

Mark

Convert list of strings to dummy variables.yxmd