For example, the user has selected 4 dates in this order:
We wish to retain this date order for the output table, but after running the formatted dates through the AttributePivoter to set the dates as attributes, they come out in alphabetical order, as follows. Note that dates 3 & 4 have switched. The order of attributes should be Jun 2019, June 2020, Sep 2018, Mar 2019.
Has anyone managed to deal with this issue?
Thanks in advance :)
Does the order matter in workbench? or only matter when the data is written out?
If thats the case then your going to want to make sure the fields are organized correctly in the writer. Fundamentally this is pretty straight forward as you can just rearranged them in an attributemanger or even the writer.
However, in your case your field values are dynamic and can change. The way I'd approach this is using a dynamic workflow and creating a schema feature (SchemaSetter custom transformer is a good start) then sorting the resulting schema (represented as a list) in the correct order based on what the user specified.
You'll then need to make sure this schema feature is passed to the writer (set up as a dynamic writer) first followed by the data features. These data features can have their attributes in any order as the order is set by the schema.
@wiltong You don't have much control over the column order coming out of the AttributePivoter.
The AttributePivoter creates a new set of attributes based on the attributes you use for the pivot in the transformer parameters. The AttributePivoter also creates a schema feature that has an attribute{} list. The attribute{} list is used by the dynamic writer to create the new schema on the target format (usually Excel!). It is the order of the elements in the attribute{} list that determines the order of the columns created by the dynamic writer in your target format. It might look something like:
The order of the elements in the attribute{} list comes from:
Group By rows: the attributes selected here will come out first - in alphabetical order I think
Group Columns By: attribute, in alphabetical order.
In the example above, the order of the dates will be ascending because we're using YYYY.MM.DD. But if you wanted your date column headings formatted as Dec 2019 , Jun 2020 , Mar 2020 , Sep 2018 then they will come out in an odd (and probably undesirable) order.
Hopefully this is something we can fix in a future FME release. Perhaps create an Idea and see if other AttributePivoter users would like to see this addressed.