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!
Userlevel 4
- 8177 replies
-
7 July 2015
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
Userlevel 2
+17
- Contributor
- 7538 replies
-
7 July 2015
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
Userlevel 2
+17
- Contributor
- 7538 replies
-
8 July 2015
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.
+2
- Participant
- 594 replies
-
8 July 2015
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
+3
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
+2
- Participant
- 594 replies
-
10 July 2015
Can you provide your email ID?
I will send the workbench which I have prepared based on spreadsheet you have provided.
+3
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?
+3
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. :(
Userlevel 1
+21
- Contributor
- 3079 replies
-
7 May 2019
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
Userlevel 1
+21
- Contributor
- 3079 replies
-
7 May 2019
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.
+3
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.
Userlevel 1
+21
- Contributor
- 3079 replies
-
8 May 2019
@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
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.