Solved

table content transformation

Z

Hello All,

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

into this:

Thanks!

icon

Best answer by fmelizard 13 August 2016, 23:27

View original

14 replies

erik_jan
Rank
Userlevel 2
Badge +16

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.

Z

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?

 

 

Z

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
Rank
Userlevel 2
Badge +17
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
Rank
Userlevel 4
Badge +13

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

Z

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?
Z
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
Rank
Userlevel 2
Badge +17
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
Rank
Userlevel 2
Badge +17
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.

 

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

 

 

@takashi, yes, I did like what you showed here. It creates new attribute "@Value(TYPE)"
Z
@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
Rank
Userlevel 2
Badge +17
@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
Rank
Userlevel 2
Badge +17
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.

 

G
Badge +3

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

This will create the attribute name by its content.

Reply


Community Stats

28,714
Posts
109,490
Replies
36,561
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.