Solved

Dynamicly create X new columns from uniqe values

  • 13 November 2019
  • 5 replies
  • 2 views

I have an input table that looks like this(input table):

Important here is that this table can change in terms of number of rows and names. So the next time the model runs it could have 5 points or 10 or 1, with random names. Names are never duplicated.

Based on the input table my fme model then creates an output table as this(table2):

I want to create a new table based on the same parameters, with a schema like this(table3):

So each row in table3 is a unique date form "DateProcessed" and each value from the coulumn "Name" from input table will get an own column in table3, containing the corresponding DistanceToIce value.

The tricky part here is that I want this to happen dynamicly, so that the number of columns that will be created in table3, and their names will follow the input table.

 

The data will never get large, maximum 10-20 points/names and 14 dates. So recreating Table3 from scratch each time the model runs would be fine.

Have read a couple of other answers/related questions here, but not found a solution that works for my problem.

icon

Best answer by virtualcitymatt 13 November 2019, 17:18

View original

5 replies

Userlevel 4
Badge +26

This is a bit tricky because the schema needs to be created during the workflow.

You could try and see if the AttributeTransposer works for you, at least to get the 'Names' as attributes.

alternatively heres a workflow I whipped up which hopefully give you what you want.

 

 

xlsxr2xlsxw.fmwt

 

 

 

This is a bit tricky because the schema needs to be created during the workflow.

You could try and see if the AttributeTransposer works for you, at least to get the 'Names' as attributes.

alternatively heres a workflow I whipped up which hopefully give you what you want.

 

 

xlsxr2xlsxw.fmwt

 

 

 

Thanks for the input. I tried your suggestion and model, but was unable to get the new attributes into the table, only visible when looking in the "Feature Information", not as coulmns in the table. I'm new to FME, so could be something I've missed.

However I solved my issue with using a small python snippet based on acrpy using arcpy.PivotTable_management. This works fast and does exactly what I need, without making the FME model more complicated.

Userlevel 4
Badge +26

Thanks for the input. I tried your suggestion and model, but was unable to get the new attributes into the table, only visible when looking in the "Feature Information", not as coulmns in the table. I'm new to FME, so could be something I've missed.

However I solved my issue with using a small python snippet based on acrpy using arcpy.PivotTable_management. This works fast and does exactly what I need, without making the FME model more complicated.

That's right, The SchemaSetter is supposed to do that, however, it creates the columns during writing. You should see (also in the feature information window) after the SchemaSetter several list attributes like fme_attribute_info{}.name or something like that.

 

 

 

The challenge with dynamic workflows like this is that FME workbench just doesn't know the schema which make it really difficult to work with. In the writer you should make sure that the Schema Source is set to get Schema From Schema Feature.

 

If its still not working you can upload your workspace and I'll make some quick notes.

 

 

That's right, The SchemaSetter is supposed to do that, however, it creates the columns during writing. You should see (also in the feature information window) after the SchemaSetter several list attributes like fme_attribute_info{}.name or something like that.

 

 

 

The challenge with dynamic workflows like this is that FME workbench just doesn't know the schema which make it really difficult to work with. In the writer you should make sure that the Schema Source is set to get Schema From Schema Feature.

 

If its still not working you can upload your workspace and I'll make some quick notes.

 

 

Thanks, that did the trick!

Userlevel 4
Badge +26

Thanks, that did the trick!

Excellent!

Reply