Skip to main content
Solved

table content transformation


Hello All,

Is there a way in FME to transform the CSV table here below:

into this:

Thanks!

Best answer by fmelizard

Interesting question. When I looked at this I was reminded of the AttributePivoter, which made a debut in FME a couple years back. It does exactly what is desired -- it can collapse a set of rows into a single one, and add new columns (attributes) whose name comes from one older column and whose value is a statistical calculation of another.

I've attached a template that does this. Note -- this only works if the "value"s are numeric. I believe this is so in the input, despite seeing a comma as the decimal separator. If not, it is easy to string substitute a , with a . , do the pivot, and then substitute back again.

It is a bit tricky to set this up and you do need to set your writer to be in "dynamic" mode, but check all the settings in the attached and they should get you pointed the right way.

pivot.fmwt

View original
Did this help you find an answer to your question?

14 replies

erik_jan
Contributor
Forum|alt.badge.img+19
  • Contributor
  • August 11, 2016

Yes:

Use the AttributeCreator (Name @Value(TYPE) and Value @Value(VALUE)) to create a new attribute with the name mentioned in the TYPE attribute.

Then use an Aggregator (group by ID, FROM, TO, PROFILE) to merge into one line.

Then create the output with all desired attributes.


erik_jan wrote:

Yes:

Use the AttributeCreator (Name @Value(TYPE) and Value @Value(VALUE)) to create a new attribute with the name mentioned in the TYPE attribute.

Then use an Aggregator (group by ID, FROM, TO, PROFILE) to merge into one line.

Then create the output with all desired attributes.

Thanks so far. In the last step, what do you mean "create the output"? You mean in the Writer or within the Aggregator?

 

 


erik_jan wrote:

Yes:

Use the AttributeCreator (Name @Value(TYPE) and Value @Value(VALUE)) to create a new attribute with the name mentioned in the TYPE attribute.

Then use an Aggregator (group by ID, FROM, TO, PROFILE) to merge into one line.

Then create the output with all desired attributes.

And will AttributeCreator (Name @Value(TYPE)) create as much new attributes as in the TYPE field? I have tried that but that doesn't work.

 

 


takashi
Evangelist
  • August 13, 2016
zhoudonghan wrote:
And will AttributeCreator (Name @Value(TYPE)) create as much new attributes as in the TYPE field? I have tried that but that doesn't work.

 

 

The AttributeCreater with this setting will definitely create a new attribute which has a name equal to the value of 'TYPE', but Workbench will not expose the name automatically. Try using the Logger to check if the attribute will be created.

 

You can use the AttributeExposer to expose the new attributes if necessary.

 


fmelizard
Safer
Forum|alt.badge.img+19
  • Safer
  • Best Answer
  • August 13, 2016

Interesting question. When I looked at this I was reminded of the AttributePivoter, which made a debut in FME a couple years back. It does exactly what is desired -- it can collapse a set of rows into a single one, and add new columns (attributes) whose name comes from one older column and whose value is a statistical calculation of another.

I've attached a template that does this. Note -- this only works if the "value"s are numeric. I believe this is so in the input, despite seeing a comma as the decimal separator. If not, it is easy to string substitute a , with a . , do the pivot, and then substitute back again.

It is a bit tricky to set this up and you do need to set your writer to be in "dynamic" mode, but check all the settings in the attached and they should get you pointed the right way.

pivot.fmwt


fmelizard wrote:

Interesting question. When I looked at this I was reminded of the AttributePivoter, which made a debut in FME a couple years back. It does exactly what is desired -- it can collapse a set of rows into a single one, and add new columns (attributes) whose name comes from one older column and whose value is a statistical calculation of another.

I've attached a template that does this. Note -- this only works if the "value"s are numeric. I believe this is so in the input, despite seeing a comma as the decimal separator. If not, it is easy to string substitute a , with a . , do the pivot, and then substitute back again.

It is a bit tricky to set this up and you do need to set your writer to be in "dynamic" mode, but check all the settings in the attached and they should get you pointed the right way.

pivot.fmwt

Thank you!

 

I noticed a writer is necessary to get the new attributes created by AttributePivot. Since I still need the results for further process, but no normal transformer can be used directly after AttributePivtor because they cannot get those new attributes, right?

takashi wrote:
The AttributeCreater with this setting will definitely create a new attribute which has a name equal to the value of 'TYPE', but Workbench will not expose the name automatically. Try using the Logger to check if the attribute will be created.

 

You can use the AttributeExposer to expose the new attributes if necessary.

 

This will create a new attribute "@Value(TYPE)"

 

 


takashi
Evangelist
  • August 17, 2016
zhoudonghan wrote:
Thank you!

 

I noticed a writer is necessary to get the new attributes created by AttributePivot. Since I still need the results for further process, but no normal transformer can be used directly after AttributePivtor because they cannot get those new attributes, right?
@zhoudonghan, if you need to handle the new attributes with subsequent transformers, you can expose those names using the AttributeExposer.

takashi
Evangelist
  • August 17, 2016
zhoudonghan wrote:
This will create a new attribute "@Value(TYPE)"

 

 

How did you enter the new attribute name? Try setting it from the menu button: Attribute Value > TYPE.

 


zhoudonghan wrote:
This will create a new attribute "@Value(TYPE)"

 

 

@takashi, yes, I did like what you showed here. It creates new attribute "@Value(TYPE)"

takashi wrote:
@zhoudonghan, if you need to handle the new attributes with subsequent transformers, you can expose those names using the AttributeExposer.
@takashi, thank you. But I saw that I need to type those attribute names instead of getting them automatically, right?,

takashi
Evangelist
  • August 17, 2016
zhoudonghan wrote:
@takashi, thank you. But I saw that I need to type those attribute names instead of getting them automatically, right?,
yup, unfortunately you need to type the attribute names manually.

 


takashi
Evangelist
  • August 17, 2016
zhoudonghan wrote:
This will create a new attribute "@Value(TYPE)"

 

 

Perhaps you are looking at the attribute name on the Workbench interface. Indeed the interface shows "@Value(TYPE)", but FME creates desired attributes internally. Try checking with the Logger.

 


gio
Contributor
Forum|alt.badge.img+15
  • Contributor
  • August 17, 2016

Manually mutate New attribute to @Value(@Value(TYPE))

This will create the attribute name by its content.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings