Select with the highest percentage and put it on comment

[Sshasnk]

I have a dataset where I have different percentages and I have to pick:

1. If the column value is greater than 80. I have to pick the column which has a comment on it

For example:  First row t1 is 80% for that we have column comment_t1 and I have to pick the value from there

2. If no column value has greater than 80 I have picked the top 2 

For example: 2nd row has values 15 (t1)  and 8 (t2), It has columns for comment comment_t1 and comment_t2 So I have to pick both the values and put it in the comment column

3. If there is only one value then I have to select the comment column for that.

Example: row 3rd where t2 has value so pick the comment_t3 column

Note: If the t1/t2/t3/t4 column does not have any value but it has a comment in columns  Comment_t1/ Comment_t2/ Comment_t3/ Comment_t4 we don't have to consider it

Input:

ID	description	t1	Comment_t1	t2	Comment_t2	t3	Comment_t3	t4	Comment_t4
123	apple	80	Alex	2.0	Shane			3	Tim
123	apple	15	Alex	8	Shane	2	Drake		Tim
245	mango		Alex	2	Shane
245	mango

Below is the sample output:

ID	description	t1	Comment_t1	t2	Comment_t2	t3	Comment_t3	t4	Comment_t4	comment
123	apple	80	Alex	2.0	Shane			3	Tim	Alex
123	apple	15	Alex	8	Shane	2	Drake		Tim	Alex,Shane
245	mango		Alex	2	Shane					Shane
245	mango									NA

[binuacs]

@Sshasnk 

[Sshasnk]

@binuacs Sorry, But your formula is hardcoded and you are not checking column t4 or t3

[saveeshkumar]

Hi @Sshasnk ,

Please find the workflow.

[SeanAdams]

Hey @binuacs - thank you for contributing to the community.

Would you be OK with spending a few mins explaining the logic of your solution - that will enable @Sshasnk and others later on to apply your good thinking to other similar problems?

Thanks again for taking the time to help others explore the power of Alteryx!

cc: @atcodedog05  @Qiu 

[SeanAdams]

Hey @Sshasnk - your situation is very specific - but I've made some notes of how to solve these kinds of problems generally.

When you have columns that are arranged like you have there - you can do complex formulae - but the problem with that is that it doesn't scale well if you end up with a growing list of pairs like this.

What you can do instead - which does scale as your number of columns grows - is to use the Transpose tool to go from data in columns to data in rows.    This is a little tricky because you want each row to have the value and the comment.

So I'd start exploring a solution that uses transpose to shape this into a long list of comments and values.

a) put on a record ID to have a unique row ID (you'll use this later)

b) Transpose but keep the record ID - now you have 3 columns, record ID; name; value

c) You can use a multi-row tool or even better an arrange tool to then arrange this into RecordID; Value; Comment

From there - it's much easier to do max & min operations across a varying number of columns.

Good luck!     Post back on this thread once you find a solution that works so that others can learn from you!

Sean