Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How can I sort a column based on the values of a different column?

cfranco
6 - Meteoroid

Hi Team,

 

I'm new to Alteryx and was wondering how can I sort a column based on the values of a different column?

For example, sort the Brand column descending based on the values in the Sales column.

5 REPLIES 5
cfranco
6 - Meteoroid
RodL,

Below is an example of what I am looking. Kind of like sorting a Pivot Table in Excel. You can sort a column based on numbers in the value fields.


Original

Brand

Item

Sales

A

Itm 1

$ 50.00

A

Itm 2

$ 10.00

B

Itm 3

$ 100.00

B

Itm 4

$ 25.00

C

Itm 5

$ 75.00




Outcome

Brand

Item

Sales

B

Itm 3

$ 100.00

B

Itm 4

$ 25.00

C

Itm 5

$ 75.00

A

Itm 1

$ 50.00

A

Itm 2

$ 10.00




Regards,

Carlos


________________________________
#####################################################################################
The information contained in this electronic mail message, including attachments, if any, is PetSmart confidential information. It is intended only for the use of the person(s) named above. If the reader of this message is not the intended recipient, or has received this message in error, you are hereby notified that any review, dissemination, distribution or copying of this communication is strictly prohibited. If you are not the intended recipient or have received this message in error, please notify the sender via e-mail and promptly delete the original message.
#####################################################################################
RodL
Alteryx Alumni (Retired)

Not sure I'm understanding the total question.

If you are asking that WITHIN the Brand column you want to sort on Sales, you can just use a Sort tool with Brand as the first sort, and Sales as the second sort.

Am I missing something? If so, can you provide an example of what you are looking for?

cfranco
6 - Meteoroid

Sorry, formating error.

 

If my data looks like this (assuming column for Brand \ Item \ Sales):

Brand A - Item 1 - $10

Brand A - Item 2 - $30

Brand B - Item 3 - $100

Brand B - Item 4 - $25

Brand C - Item 5 - $80

 

And I want it to look like:

Brand B - Item 3 - $100

Brand B - Item 4 - $25

Brand C - Item 5 - $80

Brand A - Item 2 - $30

Brand A - Item 1 - $10

 

The Sort Tool allows to sort only alphabetically?

 

 

 

RodL
Alteryx Alumni (Retired)

It looks like you are sorting on the brand based on the maximum item within that brand...in other words, the brand with the largest item should be first regardless of any other items within the brand? (So it's the 100, 80, and 30 that matters.)

Or is it the sum of all items in the brand that determine the sort? (So it's the 125, 80, and 40 that matters...i.e., the sum of the brand.)

 

Actually in either case, what I think you need to do is determine the sort of the brands based on whichever of the above is correct. (So in a sense, the two sort variables are independent of each other.) To do that you would use a Summarize tool to get either the sum or the max (or sum) value grouped by the brand. You would then sort that list separately, then assign a 1 thru N rank (you can use a Record ID tool for that) for each brand, and then join that back to your original list using brand as the key. This would put a 1 for Brand B, 2 for Brand C, and 3 for Brand A.

 

Then you just sort the entire list using the Rank value first and the dollar value second.

 

I've attached an example of how this works.

 

As to the Sort tool only sorting alphabetically, no, but if you are sorting the dollar amount like it is, Alteryx will recognize it as a String data type (due to the dollar sign). You would need to eliminate the dollar sign and convert to a number before it will sort based on the number.

cfranco
6 - Meteoroid

Thanks.  That's exactly what I was looking for (using SUM instead of MAX).  It works !!

Labels