community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Custom sort the summarized output (pivot table)

Meteor

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!

 

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

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 

Meteor

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...

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? 

Meteor

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
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

Meteor

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..

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

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