Which tool should I use for populating values on a list ( Multi-field/row or formula)

[DavidP07]

Hi guys how are you, I'm facing a little challenge in populating the correct value on the Aligned Column on a huge file. 

I need to create a formula or a logic that can help me determine the correct value that will be displayed in the Aligned Column. 

In Example A you see multiple columns, A1, A2, A3... and A Ind ( this column will agree with the rest of the other columns)

The challenge is to create a way that can help determine the correct values across the multiple columns, the rules are:

-If at least one value is populated among the A1, A2, A3... columns, therefore I will be shown in the Aligned Column, the more same values the better because that reaffirms the correct value to be displayed. 

-N/A values will agree with the other values that are in the other columns A1 A2 A3... and A Ind. 

If there is a value in the A1 A2 A3 columns... different from the one in the A Ind column, then the A Ind column will agree with the rest of the columns even if there is only one value on the A1 A2 A3.... columns.

Example A
A1	A2	A3	A4	A5	A Ind	A6	Aligned Column
ATA		ATA	ATA		ATA		ATA
ATA							ATA
ATA							ATA
ATA		ATA		ATA			ATA
ATA		ATA	ATA	N/A		ATA	ATA
		ATA			ATA		ATA
		ATA	ATA	ATA		ATA	ATA
		ATA	ATA	N/A		ATA	ATA
			ATA	ATA	N/A		ATA
ATA		ATA	ATA	ATA	ATA	ATA	ATA
ATA			ATA	ATA	ATA	ATA	ATA
			ATA	ATA	N/A		ATA
				ATA			ATA
				ATA			ATA
						ATA	ATA
ATA		ATA	ATA	ATA			ATA

In example B, there is an extra rule to be considered. 

If there is a conflicting value across A1 A2 A3... columns and they are repeated the same amount of times (in tie)  then we need to mark the Aligned Column as "needs review". BUT, if there are more values repeated values even though one or two columns disagree with it, then we will take the value that is repeated the most. 

Remember that Column A Ind will agree with the rest of the values that are shown on the other columns, if there is a specific case when the A Ind Column is the only one with a value on the entire row, then that value will be chosen to be in the aligned column.  

Please let me know if you have any questions so I can assist you and I will really appreciate your help for finding a solution to this problem, thanks a lot. have a great day. 

Example B
A1	A2	A3	A4	A5	A Ind	A6	Aligned Column
ATA		ATA	ATA		ATA		ATA
ATA					PTA		ATA
ATA							ATA
ATA		ATA		ATA	PTA		ATA
ATA		ATA	ATA	N/A	PTA	ATA	ATA
		PTA		PTA	PTA		PTA
		ATA	ATA	ATA	N/A	ATA	ATA
		ATA	ATA	ATA		ATA	ATA
			ATA	ATA	PTA		ATA
PTA		PTA	PTA	ATA	N/A	PTA	PTA
ATA			ATA	ATA	ATA	ATA	ATA
			ATA	ATA	N/A		ATA
					PTA		PTA
				PTA	ATA		PTA
						ATA	ATA
ATA		PTA					Needs Review

[DawnDuong · Accepted solution]

Hi @DavidP07 

If I interpret your problem statement correctly, basically you want the "Aligned column" to take the values that appear the most number of times across the other data columns. This is similar to finding the mode value (i.e. highest frequency) for each record, and flagging "need review" when there is a tie to the mode value.

If this interpretation is correct, one way to do is to flip the data and find Mode. Refer to this thread if you need more idea: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Finding-the-quot-mode-quot-val...

Hope this helps.

Dawn.

[DavidP07 · Accepted solution]

Hi Dawn Duong, how are you? Thanks, I will check the thread to explore more about the solution, I have a question, what can we do about column A Ind, which basically will agree on every other column? 

[Qiu · Accepted solution]

@DavidP07 
I gave a try and it turns to be a big one.
Basically, I will count the value group except the "A Ind " and if there is a Tie, then we will check again with addition the information from column "A Ind".
I add one more test case in #row 17.

[DavidP07]

Hi Qiu, thanks for putting up this workflow, I will check it out, but still, I have a little question, is there a possibility to do it with the multi-row/ field formula, or with the formula tool as well to create a logic that can verify it? Thank you.