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!
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!
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
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
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
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
Try using a combination of the AttributeCreator (or BulkAttributeRenamer) and the Aggregator, instead of the AttributePivoter.
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.
Regards,
Dario
I will send the workbench which I have prepared based on spreadsheet you have provided.
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:
The result:
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?
What else is there?
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:
The result:
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?
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. :(
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:
The result:
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?
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
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.
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.
@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
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.