Sort by CA

[tpostlewate]

Still a newby and trying to catch up on this holiday weekend. I have a file with all 50+ states. All i need to do is sort the file by the most common occurring state, which is CA. I don't want to drop any files.  Thanks!

[MarqueeCrew]

If you take the data down to a summarize, group by state and count state, you can use a join and join on state ( de-select state from right) and then sort your data on count descending. Now use a select to remove count. 

your data is supported by state with most populated at top. 

cheers,

 mark

[Qiu]

@tpostlewate 
A sample input and output would be helphere.

But maybe something like this?

[pedrodrfaria]

Hi @tpostlewate 

I posted below two examples of workflows accomplishing a sort by the most frequent state. One workflow demonstrates you can can count the frequency of which a state is present and the second workflow will do the same thing and also eliminate duplicate rows that have all the same state information and only keep the 50 states. This will give you a good basis to see how you can leverage Alteryx to accomplish this task. Feel free to provide any sample dataset in case you need more help with this task.

Pedro.

[tpostlewate]

All of the states are standardized, so output would be CA at the top of the dataset with all of the other states following in alphabetical order. What would you put in each of those tools to get your output?

[danilang]

Hi @tpostlewate 

Here's my attempt.  

The first formula tool just adds random data to make row tracking easier in the output.  In the bottom branch, count the occurrences of each state sort them descending and take the first one.  Use the Join tool as filter to select the most common records from the input. Sort the remaining ones alphabetically and union these to the most common ones, with the most common ones first, giving you

Dan

[tpostlewate]

A little more information- I am coming out of a CASS tool into a sort tool. This is how I know that CA is the most common occurring state, which is what they want the file sorted on. I have direction not to drop any records, they just want to see the most common occurring state at the top of the file (which is CA). The input record has standard name and address info on it (and a little customer data), as well as CASS information. I hope I am not making this super convoluted- I appreciate your help so much (I just don't understand it fully, yet).

[danilang]

Hi @tpostlewate 

You can't go directly into a sort tool since you have 2 different requirements, 1. the most frequent at the top and 2. the rest alphabetically.   You need someway to either split the records and union them correctly or add a second sorting field([TOP]) that will be 0 for CA and then 1 for all else.  In the second case, sort by [TOP] and then state in a single sort tool. 

In both cases you still need the logic to find the most common and split or mark the records

Dan