Custom sort the summarized output (pivot table)

[BJSOON]

Hi,

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

Default sorting in output

Cancel	Sum_Count
(Blank)	6022
1	22
10	26
11a	18
11b	201
12a	10
12b	190
12c	43
2	60
3	2
4	162
5	137
7	124
8	533

Desired sorting

Cancel	Sum_Count
1	22
2	60
3	2
4	162
5	137
7	124
8	533
10	26
11a	18
11b	201
12a	10
12b	190
12c	43
(Blank)	6022

Thank you in advance!

[MichalM]

@BJSOON 

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

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

Michal

[andyuttley]

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

Hope that helps,

Andy 

[BJSOON]

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]

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]

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

Cancel	Sum_Count
1	22
2	60
3	2
4	162
5	137
7	124
8	533
10	26
11a	18
11b	201
12a	10
12b	190
12c	43
(Blank)	6022

[andyuttley]

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]

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]

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

[andyuttley · Accepted solution]

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