Form data question

[RoyApp]

Hi folks,

I am unsure if this belongs here, or if it's even possible to solve this using Alteryx but I thought I would try my luck here!

One of the systems we use to determine which tick boxes have been selected does it in the following way, with a numeric value for each option selected. The system then holds the sum 

Option 1 - value of '2'

Option 2 - value of '4'

Option 3 - value of '8'

Option 4 - value of '16'

Upto option 10

So if option 4 and option 1 were selected then the system would just hold the numeric value of '18'. 

Instead of just holding the sum, I am trying to output to text with whichever options are selected. I.e 'Options 1 and options 4 selected'

There are way too many variations for me to manually do this and I was hoping one of you may be able to help ?

Hope you're all having a nice weekend!

Roy

[ArnaldoSandoval]

Hi Roy,

This is my first time giving support in this community, bear with me 🙂; I am attaching a workflow with two ways of handling your problem based on the description you provided;

1. You did not describe how the workflow receive the data, so I assumed rows of records with 10 options fields per row (my test was done with 4 fields, you can extend it if my approach resolve your issue.

2. I added the field "DataRow" that uniquely identifies each record, you may not need it, your data could already contains a field that identifies each record.

3. I transposed the records, columns to rows on the "DataRow" column, if your data already has a column identifying unique records use it;  The transpose tool returns 3 columns; the "unique identifier column", "Name" (Option 1, Option 2 and so on) and the "Value", either 0 or 1, or 2, 4, 8, 16 and so on.

4. the filter exclude any row having a zero, as these are unanswered options.

5. The summary tool group the records on the DataRow column (or whatever is the column in your data that unique identified a record) and concatenate the "Name" column adding a comma between different selected options, something like: Option 1; Option 1, Option 4; Option 1, Option 3, Option 4 and so on.

6. The formula in the "Work_Result_1" field does the trick, replacing the last comma with ' and '.

Please let us know if this workflow resolved your question.

[caltang]

I would suggest an analytic app to do this.

Workflow:

GUI Interface for selections:

Sample result with Option 1, 3, 4, and 6 selected:

To utilize the app, just double click the Alteryx app .yxwz and the GUI interface will appear. For you to edit it, have Alteryx open and drag the .yxwz to the Alteryx canvas to see the workflow.

Once done, click on the magic wand to see the interface:

Play around with it and let me know if this is what you want.

Best regards,

Cal

[Yoshiro_Fujimori]

@RoyApp ,

I am not sure how your system interfaces with ALteryx.

But it seems the system deals with binary numbers.

So if the interface do not need to be a "Form", I would do it as below, as it is more sclable.

If the system can interface the input to Alteryx in a string of 0 or 1 ("0101010101"), things would become very simple.

You can use BinToInt function in Formula Tool.

https://help.alteryx.com/20223/designer/conversion-functions#bintoint

Input / Output

Forumula

In your example, the value of option 1 is represented by the right most bit.

As the number starts with "2", you need to double the result of BinToInt().

Output = BinToInt([Input]) * 2

Workflow

To output the message "Option m and Option n selected", you need more steps but it is possible:

I hope this may be helpful for your case.

Good luck.

[wale_ilori]

I think between @Yoshiro_Fujimori and @ArnaldoSandoval 's responses you should be covered. I typically like to create a response key that provides a unique identifier for every response and then doing the grouping based on the responses. And if the order of the response is something to be considered ( e,g. Option 1 = 2), then I'd use the record id tool to assign a valued based on the person id (assuming several options are being collected from several people)

[RoyApp]

Thank you so much for the responses, folks, between you I have lots to explore with.

Essentially what the system does is just stores the sum instead of actually telling us which options were selected.

We then need to use the sum to figure out what options are selected, very similar to the workflow provided by @caltang but the other way around. I.E input the sum and have the workflow output which options are selected, is this something that is possible?

Thank you so much guys, I hope you have a great weekend!!

[caltang]

Hi @RoyApp,

To reverse that is a tad bit more difficult. 

We need to know the value of each option, and then if a sum is given, it gives out a list of options that adds up that value

Caveat:

1. Can the same option be chosen more than once?
2. If not, will the values of each option change at any given time?
3. What is the expected output - just a message that concatenates the outcome?

Can you provide some sample data so I can help formulate an example for you?

Best regards,

Cal

[wale_ilori]

Interesting situation.

Here's where I think things may get muddy.

If Options 1, 3 and 5 were selected and equate to a sum of 9, how do distinguish that from if option 3 and 6 were selected that also equate to 9.

Now I used a simple example to explain the issue which is how to deduce the options selected from the sum. If any combination of options selected give you a unique number of the sum then, yes it is possible but it not, then you will have situations where the sum could be any combination of options.

Hope this makes sense.

[ArnaldoSandoval · Accepted solution]

Hi RoyApp,

Thanks for giving us more information 👍

• Your complete list of options

Option 1 - value of '2'

Option 2 - value of '4'

Option 3 - value of '8'

Option 4 - value of '16'

Option 5 - value of '32'

Option 6 - value of '64'

Option 7 - value of '128'

Option 8 - value of '256'

Option 9 - value of '512'

Option 10 - value of '1024'

• Your list of value is like writing:

 2 power of (Option #), e.g. binary numbers.

The Alteryx inputs your are processing are even numbers, it is not possible for you to get an Odd number,

For further references check this article: Binary Number System , scrool down until you reach this on that page:

Visualising your question:

Visually, your question looks like this:

Alteryx's IntToBin is the way to handle your problem, its help is a bit shallow : Conversion Functions 

The Results:

1. The first part of the workflow (Parsing Sums into R_Results) deals with the sum and applies the IntToBin function, I kept the intermediate function to reach R_Results)
2. The second part integrates with the solution 1 posted earlier this week.

Hope this helps,

Arnaldo