6

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.

enter image description here

6

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.

enter image description here

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.

enter image description here

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.

enter image description here

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.

enter image description here

So, the input CSV looked like this:

enter image description here

And the final CSV looked like this: enter image description here

And the FME workbench looked like this:

enter image description here

4

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:

enter image description here

Now add an Aggregator. Set it to group by Parcel_no and set Accumulation Mode to Merge Incoming Attributes.

enter image description here

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:

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.