Sort by value of data

khayitof

Hi Alteryx pros!

I have a table:

Acc | Amount|

2200 9000

2300 800

2400 70000

I need to sort it out so that the result should look like:

Acc | Amount|

2400 70000

2200 9000

2300 800

The logic is from Largest value to the smallest or vice versa.

I'm getting result where it is sorted as:

Acc | Amount|

2200 9000

2300 800

2400 70000

The logic of which is the start number of the data I guess.

Please help!

Thank you in advance!

Custom Tools

Common Use Cases

Data Investigation

Accepted answers

grazitti_sapna

Hi @khayitof,

I walked through the workflow that you have shared but it seems you forgot to share the input file.

I saw the worflow and it seems that although you are doing to_number, the output field type of the f17 column is still V_Wstring. Below is my solution to the problem.

1) use trim function before doing to_number to eliminate spaces if any

2) use a select tool to change the data type of F17 to int or double and then re run the workflow.

Please find attached the sample screenshot for your reference.

Let me know if this helps.

Sort.png

sort2.png

sort 3.png

All comments

alts2h

Please check that the Data type of your Amount column is numeric.

Otherwise, it will sort it alphabetically...

And, in an alphabetical order, the string "9000" is greater than "700000"

khayitof

Thank you for response!

Originally it is not numeric.

I used the following formula to convert:

ToNumber(replace(replace([Filed], ".",""),",",".")) and the tried to sort it in the same workflow.

In any circumstances it gives the same alphabetic sort. Any more suggestions?

Blake

Hi @khayitof - check the box that says "Use Dictionary Order" on your sort tool and the string values in your field will sort as you expect them to.

Even though they look like numbers, they are string values and get sorted by the first digit.

i.e the list of 1,2,3,4,10,100 will be sorted 1,10,100,2,3,4 if you do not check the box.

Thanks, let me know if this works for you.

khayitof

Hi.

The "Use dictionary Order" box is unticked. As you said my data type is string: smthng like: 12.345,00

And I used ToNumber(replace(replace([F17], ".",""),",",".")) formula to convert it to numeric.

Before converting to numeric it looked like the same as at F16. The outcome of workflow if on F17 column.

alts2h

Seem you have trailing and/or leading spaces.

Can you show us the metadata associated with the view by clicking the metadata button in the result browser?

khayitof

Here is workflow. What I need is to identify top 20 values from field F17. Please help!

Identify top 10 values.yxmd

grazitti_sapna

Hi @khayitof,

I walked through the workflow that you have shared but it seems you forgot to share the input file.

I saw the worflow and it seems that although you are doing to_number, the output field type of the f17 column is still V_Wstring. Below is my solution to the problem.

1) use trim function before doing to_number to eliminate spaces if any

2) use a select tool to change the data type of F17 to int or double and then re run the workflow.

Please find attached the sample screenshot for your reference.

Let me know if this helps.

Sort.png

sort2.png

sort 3.png

khayitof

Hi,

Thank you so much. I didn't notice that data type was still string!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335