Skip to main content
Solved

Replicate in FME this Excel example

  • August 9, 2017
  • 4 replies
  • 20 views
C
Forum|alt.badge.img

I have an example of something I can do in Excel but looking to do in FME. Please refer to the PDF attachment....

The first image shows a snapshot of a fictional table of data. Each employee has a unique "EmployeeNumber" and also a unique "CardNumber". The field called "Event" shows a record of every instance over a week, where an employee has either "swiped in" their card to enter an office building, or has "swiped out" to leave. So for example, Hannah has used her card 5 times in the week to either enter (swipe in) or leave (swipe out) the office.

The second image shows a pivot table in Excel, which sums the number of times an employee has either swiped in or swiped out. I have then added the field called "Difference" as I simply wish to compare the number of times an employee has swiped in compared to when they have swiped out. And I am especially interested where the difference is 1 or more.

My query is this ; can FME replicate what I have done in Excel in this example ? (I would also want all other fields, such as EmployeeNumber and CardNumber to be included in the final output too)

Thanks

Best answer by fmelizard

Hi @cliff_wade, you sure can use FME to output something like you've detailed in your PDF. I've attached a workspace (created in 2017.0) demonstrating how to use some of the transformers that @gio has mentioned.

sample-workspace-table-pivot.fmwt

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • 2252 replies
  • August 9, 2017

@cliff_wade

Hi,

Yes you can pivot and sum.

There is a pivot transformer, with sum aggregation.

There are examples on the forum and in the knowledge centre.

You'll have to expose attributes yourself.

There are also examples on the former using different techniques.

Search for pivot or transpose on the forum.

(if your data is in a oracle database, oracle sql also has a pivot function)

fmelizard
Safer
Forum|alt.badge.img+20
  • fmelizardSafer
  • Safer
  • 3719 replies
  • Best Answer
  • August 9, 2017

Hi @cliff_wade, you sure can use FME to output something like you've detailed in your PDF. I've attached a workspace (created in 2017.0) demonstrating how to use some of the transformers that @gio has mentioned.

sample-workspace-table-pivot.fmwt

courtney_m
Contributor
Forum|alt.badge.img+19
  • courtney_mContributor
  • Contributor
  • 127 replies
  • August 9, 2017

If you have the first image in Excel already, you could read it into FME using the Excel Reader. Then, use an AttributePivoter - Group rows by Name, Analyze Event, Group Columns by Event, and Statistic type of Count (Grouping by column splits up the results for each unique value of the attribute specified). Then Expose the new attributes (Swipe In and Swipe Out). Then merge the pivot with the original data, based on Name (make sure the pivot is the requester).

This is the solution I got - which you can send to an Excel writer:

Please see attached for the workspace I created.

-Courtney

C
Forum|alt.badge.img
  • cliff_wade
  • Author
  • 6 replies
  • August 10, 2017

Hi @cliff_wade, you sure can use FME to output something like you've detailed in your PDF. I've attached a workspace (created in 2017.0) demonstrating how to use some of the transformers that @gio has mentioned.

sample-workspace-table-pivot.fmwt

Hi TiaAtSafe, many thanks for that. However, I have two questions - (a) could you clarify which attribute I need to expose in AttributeExposer and (b) my version of FME (2015.1) does not have the AttributeManager - is there another transformer I could use instead ? Thanks

 

 

Community Stats

32,058
Posts
121,723
Replies
39,686
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.