How to sort a pivot table

I’m having a list with values and timestamps. From the timestamp I did already extract the month as value for the pivot. Now I’m in the process of creating a pivot table from that data with monthly aggregated values.
The name of the pivot cols is “x - mean” with x being the month from 1 to 12 (e.g. “1 - mean” or “12 -mean”).
That works quite well except that the columns in the pivot are randomly sorted.
When switchin on the lexicographically sorting the month are ordered 1 / 10 / 11 / 12 (as expected).

Is there a simple trick / setting how to enforce the comlums to be sorted by value of the month?
Or do I need some of these nice workarounds like convert value to string and ensure leading “0”?

@knimediger, can you provide your workflow and some example data?

I can’t quite picture what’s happening here, just from your description.

The naming scheme for the pivot columns doesn’t match the typical KNIME one, and when I created some mock data I had no issues with the columns being sorted correctly.

2 Likes

Hi @knimediger

You can use the Column Resorter node.
gr. Hans

4 Likes

Thanks for your support. Please appologise the late response.

@elsamuel
The input data looks like
price | datetime | year | month
1 | 1.1.2021 11:00 | 2021 | 1
2 | 1.1.2021 12:00 | 2021 | 1
1 | 1.2.2021 11:00 | 2021 | 2
3 | 1.12.2021 11:00 | 2021 |12
I want to see the avg price per month.

So the pivot table has columns named

year | 1-mean | 12-mean | 2-mean

@HansS
Thanks, that ist solving my issue as I have all 12 months in my list.
But I’m wondering what this node would do it you start for example with the months Jan to July and then extend further to October, November and December. In the config I would only have the first columns to select for the sorting process.
But that’s just a theoretical question, no real answer required

2 Likes

It’s still unclear to me what the problem is. I used your data and this was the result:

image

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.