Skip to main content
  • EN

Hello,

I am trying to create what I would call a pivot chart in excel using FME, I have managed to input my data which represents the number of exports from a certain area of a building by date&year; (similar to below - however my table has a lot more rows as it runs to 2030).

DateAreaNumberUnitsExported01/01/2020Area 1501/01/2020Area 2401/01/2020Area 31001/02/2020Area 1201/02/2020Area 2801/02/2020Area 31101/03/2020Area 1101/03/2020Area 2501/03/2020Area 38

Basically I am trying to get my data to look like this:

-Area 1Area 2Area 301/01/2020541001/02/2020281101/03/2020158

so that I have the date running down the side, the Area Number as the table header and the units exported in the relevant cell corresponding to the correct area and date. I would then like to export this as an excel file

How should I go about achieving this?

Thanks,

Tom

Build a list based on date, create new attributes named from the AreaNumber with the value of Units Exported, expose the newly created attributes

excel-pivot.fmwt

That's great, exactly what I wanted! I've noticed now that I have gaps within my original dataset

so as an example looking at the dummy data I sent in its raw format 01/04/2020 may have no records for unit exports from Area1-3 and so does not contain the three rows of data. is there a way of inserting missing rows to my final table?

Thanks

Build a list based on date, create new attributes named from the AreaNumber with the value of Units Exported, expose the newly created attributes

excel-pivot.fmwt

@egomm That's great, exactly what I wanted! I've noticed now that I have gaps within my original dataset

 

so as an example looking at the dummy data I sent in its raw format 01/04/2020 may have no records for unit exports from Area1-3 and so does not contain the three rows of data. is there a way of inserting missing rows to my final table?

 

Thanks

 

 

@egomm That's great, exactly what I wanted! I've noticed now that I have gaps within my original dataset

 

so as an example looking at the dummy data I sent in its raw format 01/04/2020 may have no records for unit exports from Area1-3 and so does not contain the three rows of data. is there a way of inserting missing rows to my final table?

 

Thanks

 

 

Are your dates always the 1st of the month and you want output in 1 month intervals?

 

 

That's great, exactly what I wanted! I've noticed now that I have gaps within my original dataset

so as an example looking at the dummy data I sent in its raw format 01/04/2020 may have no records for unit exports from Area1-3 and so does not contain the three rows of data. is there a way of inserting missing rows to my final table?

Thanks

excel-pivot-2.fmwt

 

 

One possible way if you want an entry for every month

 

 

Build a list based on date, create new attributes named from the AreaNumber with the value of Units Exported, expose the newly created attributes

excel-pivot.fmwt

I realised I could have explained my problem a little better, so I have posted a separate question on the forum @egomm . Feel free to take a look - thanks.
I realised I could have explained my problem a little better, so I have posted a separate question on the forum @egomm . Feel free to take a look - thanks.
Does the second example I posted not deal with this correctly?

 

 

That's great, exactly what I wanted! I've noticed now that I have gaps within my original dataset

so as an example looking at the dummy data I sent in its raw format 01/04/2020 may have no records for unit exports from Area1-3 and so does not contain the three rows of data. is there a way of inserting missing rows to my final table?

Thanks

This is very useful Thank you - I have one related question for folk out there.

 

I have a very similar situation which I'm dealing with but I don't necessarily know, in advance, how many Area Numbers there will be. has anyone had to solve such a situation?

Reply


Community Stats

30,186
Posts
114,572
Replies
37,849
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.