Solved

Replicate in FME this Excel example

  • 9 August 2017
  • 4 replies
  • 4 views
C
Badge

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

icon

Best answer by fmelizard 9 August 2017, 17:36

View original
1 Attachment

4 replies

G
Badge +3

@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
Rank
Userlevel 4
Badge +13

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

C
Badge

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
Badge

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

 

 

Reply


Community Stats

28,709
Posts
109,481
Replies
36,554
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.