Simple binary /finding the common number

[h12]

Hi all

I have the following binary numbers:

00100

11110

10111

00111

10000

The most common first bit is 1 (there are 3 of them in the first column) and the least common is 0.

The same across each digit column would be 0,1,1,0 (most common) - giving me a total of 100110

Q- is there a way I can find these common digits on Alteryx for a larger set of numbers, and then remove the numbers which do not follow the common number:

Digit 1 is common, so i would keep 10000, 11110,10111, then check the second digit, see whats common and iterate/remove again

Many thanks

[Christina_H]

Here's a macro as a starting point, although I'm not sure it's exactly what you want.  It checks for the most common first digit, keeps only records matching that digit and moves on to the next.  If both are equally common I think the sort will always take 0.

However this does not give you 10110, it gives 10000.  This is because it's not checking the most common second (and subsequent) digits from the whole set, only from those that match the most common previous digits.

[h12]

Thank you for getting back to me - sorry my initial ask wasn't clear, but I think the macro you shared is along the right lines!

00100

11110

10111

00111

10000

1. Common digit per 'column'. In the above binary numbers, there are 5 columns, so if you look down the first column (in bold below):

00100

11110

10111

00111

10000

You can see number 1 appears 3 times and hence is the most common digit in this column. You would then repeat this for the remaining columns (column 2 as an example below)

00100

11110

10111

00111

10000

Where number 0 appears 4 times so is most common. Going through all 5 columns the final binary/value would be 10110.

2. Start with the first column, and see which number appears most common. Using the example above, we identified number 1 appears the most. So then, I would like to remove all the binary rows which do not start with 1:

From:

00100

11110

10111

00111

10000

To:

11110

10111

10000

So I a now left with three rows. Here, I would now look at the second column of digits and see what value is common. In this case its 0, so I would want to remove the rows which don't have a 0

From:

11110

10111

10000

To:

10111

10000

This continues until I have one number left. If the count is the same, I can use 1 or 0 (I think in the macro above you used 0 which is fine/can be edited depending on the requirement)

[Christina_H]

Oh, so you want both versions?  The macro I shared previously covers option 2 (with optional edits on the sorting to select 1 instead of 0 when they're equal).  Here's another macro for option 1.  It outputs the common digits one at a time with another column identifying the position.  You would need to concatenate them outside the macro to get the final binary string 10110

[Ben_H · Accepted solution]

Hi @h12,

I think this should do the trick -

I tokenise each binary string, and with a few record IDs you can summarise to get the most common value for each column of values.

Regards,

Ben

[h12]

Thanks @Christina_H 

Looking at your second macro, this works for finding the most common number in the first column of digits, how would I determine the most common second digit?

Also, with the first macro you shared, I was expecting there to be only one number at the end.. Do you think theres an issue with this part of the flow:

[h12]

Thank you @Ben_H This works for my first query!

[patrick_digan · Accepted solution]

@h12 You may want to try the advent of code tag, there is a post setup for each day. Here is today's post.

[h12]

Thank you soo much! Exactly what I was looking for - didnt know this page existed @patrick_digan. @Christina_H Thanks for your help on this.

Below is how I solved the question, was looking for an efficient way of doing it! 😂