I have some data that looks like the first table. I usually use Microsoft Access to create a cross-tabulation as in the second table. Does anyone have an idea of how I could accomplish that in FME?
Thanks!
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
- 8394 replies
Hi
Have you looked at the AttributePivoter (http://docs.safe.com/fme/html/FME_Transformers/FME_Transformers.htm#Transformers/attributepivoter.htm)?
It was discussed here quite recently (https://safecommunity.force.com/communityanswers?feedtype=RECENT_REPLY&dc=FME_Desktop&criteria=BESTANSWERS&#!/feedtype=SINGLE_QUESTION_DETAIL&dc=FME_Desktop&criteria=BESTANSWERS&id=906a0000000dC46AAE).
David
Dear David,
I have tried the AttributePivoter and it doesn't work. The reason is because the parameter "Attribute To Analyze" has to be numeric and, in my case it is the Designation thing.
Not really sure what I am trying to do is feasible with FME to be honest. If you have any ideas of how it could achieved, they would be much appreciated.
Darius
- 7843 replies
Hi Darius,
The AttributePivoter can also be used to pivot a table by a string column in some cases like yours. Try setting Max (or Min) to the "Pivot Summary Statistic Types" parameter.
In addition, you will have to expose the newly created attribute names (Blue, Green, Purple, Red) with the AttributeExposer, if you need to manipulate them with other transformers.
However, as you say, since the transformer seems to be designed for calculating statistics for a numerical column, the usage mentioned above might be non-recommended. If you feel uneasiness, you can use a combination of AttributeCreator (or BulkAttributeRenamer) and Aggregator in this case. Regarding the way, see the link that David pasted.
Takashi
Hi Takashi,
The AttributePivoter fails regardless what Pivot Summary Statistic Type you choose (Min, Max or any other). The errors seem to be same. As indicated above, it has to see with the fact that the attributtes are string and no calculations are possible. Not sure this transformer can give what I need :(
The five attributes to group by are the species related information (in the first example I only showed Species & Group).
Darius
- 7843 replies
I cannot specify the reason for the error because your first table is not the same as the actual data.
Try using a combination of the AttributeCreator (or BulkAttributeRenamer) and the Aggregator, instead of the AttributePivoter.
+12- Contributor
- 605 replies
Hi,
This is an alternative way, if your unique category values are less orelse we have to create numerous attributes based on column
This is an alternative way, if your unique category values are less orelse we have to create numerous attributes based on column
+15- Contributor
- 2252 replies
You can solve this by using aggregator and/or listbuilder &co in conjunciotn with attributeexploder.
Then create a txtfile with a header line (at texlinenumer=1 and subsequent datalines (nrs >=2 ). Write to textfile.
Read textfile and write to excell (this automatically exposes the created attributes). (this latter can be automated using a workspacecaller)
A couple of these type workspaces in this forum doing these kinds of table-transforms.
Thanks to everybody who replied to this but I can't get my head around your suggestions. I have tried most of them I don't get any closer to what I need as an ouptut, so I give up.
Regards,
Dario
+12- Contributor
- 605 replies
Can you provide your email ID?
I will send the workbench which I have prepared based on spreadsheet you have provided.
+4- Contributor
- 28 replies
Sorry for digging up an old thread, but I'm stuck on the same problem and can't find a good solution for this.
I'm trying to convert an IFC file to an Excel.
Something like this:
- Import IFC
- GeometryPropertyExtractor for Pset.Attribute
- AttributeExploder for "_attr_name" and "_attr_value"
- So far no issues:
The result:
- Neat, but how do I pivot (crosstab, group by "GlobalID") this?
I can filter unique Attribute Names (_attr_name), export it as a csv and import for the AttributeExposer and that works:
But I really need to do this more dynamically.
The question is, how?
- The AttributePivoter doesn't work with strings (for some crazy reason).
- I was suggested a SQL querry using InlineQuerier? Unfortunately I'm hopeless with SQL
- I'm okay with R, but the RCaller is kind of ugh, need to research this more.
What else is there?
+4- Contributor
- 28 replies
Sorry for digging up an old thread, but I'm stuck on the same problem and can't find a good solution for this.
I'm trying to convert an IFC file to an Excel.
Something like this:
- Import IFC
- GeometryPropertyExtractor for Pset.Attribute
- AttributeExploder for "_attr_name" and "_attr_value"
- So far no issues:
The result:
- Neat, but how do I pivot (crosstab, group by "GlobalID") this?
I can filter unique Attribute Names (_attr_name), export it as a csv and import for the AttributeExposer and that works:
But I really need to do this more dynamically.
The question is, how?
- The AttributePivoter doesn't work with strings (for some crazy reason).
- I was suggested a SQL querry using InlineQuerier? Unfortunately I'm hopeless with SQL
- I'm okay with R, but the RCaller is kind of ugh, need to research this more.
What else is there?
ifc2csv2.fmwTest IFC.zip
Test files, to speed things up.
Also, the TLDR version:
How to?
Every basic ETL tool does this out of the box. :(
+44- Influencer
- 3427 replies
Sorry for digging up an old thread, but I'm stuck on the same problem and can't find a good solution for this.
I'm trying to convert an IFC file to an Excel.
Something like this:
- Import IFC
- GeometryPropertyExtractor for Pset.Attribute
- AttributeExploder for "_attr_name" and "_attr_value"
- So far no issues:
The result:
- Neat, but how do I pivot (crosstab, group by "GlobalID") this?
I can filter unique Attribute Names (_attr_name), export it as a csv and import for the AttributeExposer and that works:
But I really need to do this more dynamically.
The question is, how?
- The AttributePivoter doesn't work with strings (for some crazy reason).
- I was suggested a SQL querry using InlineQuerier? Unfortunately I'm hopeless with SQL
- I'm okay with R, but the RCaller is kind of ugh, need to research this more.
What else is there?
If the end goal is excel then you don't necessarily need to expose the attributes on the canvas, because a dynamic schema can take care of that part. Do you have an example of the initial input that you can share?
I usually pivot data like this by using attribute values to create the attributes then aggregating and altering the schema
e.g.
pivot_to_excel.fmwt
+44- Influencer
- 3427 replies
ifc2csv2.fmwTest IFC.zip
Test files, to speed things up.
Also, the TLDR version:
How to?
Every basic ETL tool does this out of the box. :(
Looking at this data, there is no need to use an attribute exploder and then try and pivot the data, you just need to dynamically write to excel. I'd suggest posting as a new question.
+4- Contributor
- 28 replies
Looking at this data, there is no need to use an attribute exploder and then try and pivot the data, you just need to dynamically write to excel. I'd suggest posting as a new question.
@egomm Hello, thanks for the reply.
Yeah, I'm kind of stuck in this ETL way of thinking. I have to do some research on writing attributes dynamically.
But your pivot_to_excel.fmwt looks interesting.
Could I bother you to tweak the workspace to make it work with this file? IFC Example.csv
As a learning exercise. :)
My secondary goal here is to push the IFC data to Azure for the ML Studio, that's why the pivot part is so important.
+44- Influencer
- 3427 replies
@egomm Hello, thanks for the reply.
Yeah, I'm kind of stuck in this ETL way of thinking. I have to do some research on writing attributes dynamically.
But your pivot_to_excel.fmwt looks interesting.
Could I bother you to tweak the workspace to make it work with this file? IFC Example.csv
As a learning exercise. :)
My secondary goal here is to push the IFC data to Azure for the ML Studio, that's why the pivot part is so important.
You're only needing to 'pivot' the data because of the attribute exploder. Your start point is one feature per row and you want to end up with one feature per row.
I think you just need to handle dynamically writing the attributes, example using your sample file
ifc_to_excel.fmwt