Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Yes / No text columns to boolean columns

Highlighted
6 - Meteoroid

I am trying to create a macro that can dynamically 

1. Finds all columns containing only "Yes" or "No" values

2. Converts those columns to Boolean (True for "Yes and False for "No")

3. Maintains column order

 

I have managed to do step 1 in the attached macro, but I'm stuck on step 2.

 

I believe I need to apply a formula tool dynamically to a set of columns that get selected in step 1 - is there any way to do this?

Highlighted
14 - Magnetar
14 - Magnetar

You should be able to use a Multi-Field tool to accomplish this! 

 

1. Select the columns that contain your Yes/No answers

2. Uncheck the box for "Copy Output Fields Add"

3. Check the box to "Change Output Type to" and select Bool

4. Use the expression IIF([_CurrentField_]="Yes",1,0) to convert Yes to 1 and No to 0 . NOTE: if you have null options in there that need to stay null, then add another IF clause to handle, such as IIF([_CurrentField_]="Yes",1,IIF([_CurrentField_]="No",0,Null()))

 

See attached for an example! Hope that helps!

 

Cheers,

NJ

 

Highlighted
14 - Magnetar
14 - Magnetar

Hey @mgoldwassertrp! Is it necessary to create a macro? You could just add a Multi-Field Formula tool after the input with the following expression:

 

iif([_CurrentField_]="Y" || [_CurrentField_]="Yes","True",iif([_CurrentField_]="N" || [_CurrentField_]="No","False",[_CurrentField_]))

I selected all Text fields to apply that expression to and did not copy output fields as new fields. You might have to increase the size of the field and then use a Select to change the field type after this, but this should get you the true and false values in any text columns. Hope this helps!

Highlighted
6 - Meteoroid

@NicoleJohnson thank you - I want to create a reusable macro that will auto detect which columns are filled with "Yes" and "No" values, and use that list of columns to feed into the the multifield formula tool. I think I solved the first piece (auto detecting yes/no columns) and you have the third piece (a multi-field formula), but we are missing the second piece, which connects the two 

Highlighted
6 - Meteoroid

@BarnesK Interesting - this might work, but I was hoping to change the column type to boolean (or single byte, or int) as well.

 

Can we also change the column type for those columns that get modified?

Highlighted
14 - Magnetar
14 - Magnetar

@mgoldwassertrp I forgot about the Auto Field tool! If you add this after the Multi Field formula, that should change any field with true/false values to Boolean automatically.

So you should really only need the input, Select (just to make field lengths long enough to hold true/false values), Multi Field formula, Auto Field, and output.

Labels