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

Question

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       A1A2A3A4A5A IndA6Aligned ColumnATA ATAATA ATA ATAATA      ATAATA      ATAATA ATA ATA  ATAATA ATAATAN/A ATAATA  ATA  ATA ATA  ATAATAATA ATAATA  ATAATAN/A ATAATA   ATAATAN/A ATAATA ATAATAATAATAATAATAATA  ATAATAATAATAATA   ATAATAN/A ATA    ATA  ATA    ATA  ATA      ATAATAATA ATAATAATA  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       A1A2A3A4A5A IndA6Aligned ColumnATA ATAATA ATA ATAATA    PTA ATAATA      ATAATA ATA ATAPTA ATAATA ATAATAN/APTAATAATA  PTA PTAPTA PTA  ATAATAATAN/AATAATA  ATAATAATA ATAATA   ATAATAPTA ATAPTA PTAPTAATAN/APTAPTAATA  ATAATAATAATAATA   ATAATAN/A ATA     PTA PTA    PTAATA PTA      ATAATAATA PTA    Needs Review

DawnDuong · Accepted Answer

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-value-for-text-across-columns/td-p/893912

Hope this helps.

Dawn.

DavidP07 · Accepted Answer

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 Answer

@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.