I have a table information and that needs to be transposed like shown below screen shot.
Parcel number is maybe more then 1000 and each parcel bldg type more then 30 types.
Please can advise to achieve this task.
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
This was an interesting one and it isn't as straightforward as I had thought it would be.
I created a CSV file that is identical to your screenshot. I created a new FME workbench with a CSV reader. Then, I pass all features into an AttributeExploder. I set the exploding type to List.
Then I passed the features into an AttributePivoter. I grouped rows by Parcel_No, columns by Bldg_Type, analysed Count and set the statistic type to Sum.
This is essentially all I needed to do. The results appear in the data inspector. However, if I write the results to another CSV, the column values are missing. So, the other thing I did was to expose the attributes with an AttributeExposer.
The only problem, is you have to know the values of the building types before hand. If you know these, then great. If not, you may need to work out another way to get all these values, possibly write them to another table, then import them to the AttributeExposer.
Also, in between the AttributePivoter and the AttributeExposer, I put an AttributeRemover to get rid of Bldg_Type, and Count as well as the list attributes.
So, the input CSV looked like this:
And the FME workbench looked like this:
OK, the data part is easy, but writing it takes a little finesse.
To enable dynamics writing add two new columns to your source data. Column X (all rows have a value of MyTable) and Column Y (all rows have a value of fme_varchar(20)).
Now add a Reader and place an AttributeManager to create a new attribute. For the name enter @Value(Bldg_type) and for the value select the count attribute:
Now add an Aggregator. Set it to group by Parcel_no and set Accumulation Mode to Merge Incoming Attributes.
Run that to a Logger or Inspector and you'll see the data is correct. We just need to write it out.
To write the data select Reader > Add Reader as Resource. Add the reader as format Schema From Table and have it read the source data.
In the parameters dialog, set Feature Type = Column X, Attribute Name = Bldg_type, and Attribute Data Type = Column Y.
Now add a Writer. Set the attributes definition to Dynamic. Set the Schema Source to be the Schema From Table resource reader:
Run the workspace. It should give you what you need.
The advantage to this method is that it works regardless of what building types you have (you don't need to know them in advance)
I am writing this on my phone on a bouncy train - so apologies for any spelling issues - now I'm back in the office, I've added screenshots and copied the workspace to Dropbox.