Toad World® Forums

Move Pivot Table Column Values (Like Excel)

Excel pivot tables, allow you to grab and move the field values to place in a non-alphabetical order. While it is possible to 'hide' and sort column associated with the value order, it is much simpler to simply click and move the field value to an up/down or left/right location.

I'm not sure what you mean by "move field values".

Toad's pivot grid lets you drag-drop to rearrange columns. Is this what you mean?

Oh... Sorry jdorlon, I should have been more clear. As an example a field containing Student Class Level - should be Freshman, Sophomore, Junior, Senior; but it comes out in alphabetical order like Freshman, Junior, Senior, Sophomore. In excel, you can grab the field values and just move them with the Student Class Level field. Or in your case above, what if you wanted the line with DeptNo 20 first, followed by 10 and 30.

As a follow up: I know I can add a "sort order field" to all my files, but in most cases, this isn't practical - which is what made Excel a better choice.

I'm just seeking to stay within Toad Data Point as much as possible to avoid data errors.

Oh, well if you're talking about Toad Data Point then I'm off the hook! This a Toad for Oracle forum. I'll move the thread.

1 Like

Ahh. Nuts! image
I thought I was in Data Point? Sorry!!

No worries. It did say "Toad for Oracle Idea Pond" there, I just switched it. Anyway, hopefully the TDP folks can make this happen.

1 Like

Hi Rochelle,
currently you cannot do it in TDP but we're going to investigate whether it would be possible.
How do you do it now? Do you use any workaround to do that?
br,
dan

Good morning Dan -

I work for a university, so it's imperative that we have data in a specific (non-alphabetical) order. I can either dump my TDP data into Excel/Pivot to click and drag fields to where I need them (e.g., Freshman, Sophomore, Junior, Senior...). Or use Hyperion pivot tables which also has this ability to click and rearrange field values, but my goal is really to stay within TDP as much as possible.

Yes, I can create an external table with the field values and a "sort_order" column: Freshman has a 1 in the sort column, Sophomore has a 2 sort column, etc., then connect the table to the master table, use the sort_order field to sort then export the data. But this seems like a long winded solution, to what should be a click and drag solution. Bottom line, is that the sort function needs options for automatic (asc/des) and manual ordering.

I hope this helps -
Rochelle

Hi Rochelle,
we opened a new CR: QAT-15148 for custom ordering of column values.
Until we fix it try adding a column in Data Cleansing that would be based on the categories you mentioned. The new value should order the categories like you wanted.

1 Like

My workaround for now is to add an additional column that will be sorted upon. Usually this isn't terribly difficult, but having to do it EVERY time is annoying. :slight_smile: