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

Custom sort the summarized output (pivot table)

BJSOON
8 - Asteroid

Hi,

I would like to find out a way to custom sort my summarized output.  

 

Default sorting in output

 

CancelSum_Count
(Blank)6022
122
1026
11a18
11b201
12a10
12b190
12c43
260
32
4162
5137
7124
8533

 

Desired sorting

CancelSum_Count
122
260
32
4162
5137
7124
8533
1026
11a18
11b201
12a10
12b190
12c43
(Blank)6022

 

Thank you in advance!

 

13 REPLIES 13
MichalM
Alteryx
Alteryx

@BJSOON 

 

You can force this order by splitting the data into two streams using the Filter tool

 

filter-empty.png

 

And then bringing these back together using Union and selecting Set a Specific Output Order

 

union.png

 

Michal

andyuttley
11 - Bolide
11 - Bolide

Hi @BJSOON 

 

Two ways to do this, which are attached:

 

1 - you can change the sort setting by selecting 'use dictionary order', which works for your dataset

2 - you can extract the int and sort on both fields

 

Both methods attached

sort.PNG

 

 

Hope that helps,

 

Andy 

BJSOON
8 - Asteroid

Hi, I'm still unable to get the desired output.  May be it has to do with the data type.  The "Cancel" field has an data type attribute of 'V_WString" and size 1073741823.  I tried to change it to 'String' type but got an error message "unable to allocate 1073741823 bytes of memory..

 

I don't know what was missed...

MichalM
Alteryx
Alteryx

Which of the methods have you attempted @BJSOON? And when you say you're unable to get the desired output, what is the output that you're getting? 

BJSOON
8 - Asteroid

Hi, referring to my first message in this thread, the sorting (refer to Cancel column) is still not according to what I hope to achieve:

 

Desired sorting

CancelSum_Count
122
260
32
4162
5137
7124
8533
1026
11a18
11b201
12a10
12b190
12c43
(Blank)6022
andyuttley
11 - Bolide
11 - Bolide

Hi @BJSOON 

 

Have you tried my solution (couple of messages above)?

I have just checked and believe both methods I posted give you the desired solution you've posted. 

 

Andy

BJSOON
8 - Asteroid

Hi Andy, yes I tried both the methods but could not get the desired sorting.  So I thought it might be due to the data type to. The "Cancel" field has an data type attribute of 'V_WString" and size 1073741823.  I tried to change it to 'String' type but got an error message "unable to allocate 1073741823 bytes of memory..

 

I am not sure what I missed..

MichalM
Alteryx
Alteryx

I focused on the blank and missed the values in the Cancel column being wrong in my original post, apologies.

 

What @andyuttley built seems to do the job. An alternative approach would be to 

 

  • Use RegEx Parse to split the column into Numbers and Letters
  • Use the Number to sort on
  • Split the data into blanks and non-blanks and bringing them back together using Union in a specified order as before

pivot-table.png

andyuttley
11 - Bolide
11 - Bolide

@BJSOON 

It sounds like your error is not to do with the logic, but the size of the field(s) you’re feeding in. Can you try using an autofield tool after your input, to ensure the fields are optimal size and type? It’s probably also worth testing against a smaller subset of data first – to check the logic (the logic should still work – it just sounds like it’s trying to handle much larger file now). Feel free to send over a packaged version so I can test myself if easier

 

Though I'd definitely try @MichalM solution too!

Labels