Question

How to filter excel file with formulas without messing up the output numbers using FME

  • 30 June 2017
  • 2 replies
  • 27 views
G

I'm new to FME and I'm creating a workbench using two excel files. One has all the data with formulas from information column (Region, Strat, Just Code) to formulas -- simple Sum (A+B), Percentage to complex IF statements AND the second excel file contains info which helps me filter using certain categories (Just Code in this case). I used Feature Merger to merge the two excel files, then Tester and then Test Filter to help me create output based on Strat using Just Code (For eg: If Just Code (in 2nd excel) is 1 or 2, Output that as Poles). I opted for 'Keep formulas' while first creating the Reader but when I checked the output, all my excel numbers with formulas are messed up). In the excel file 'original sheet', Column G (row 10) is = F10+AI10-E10 but after the filter the output which is now in Column G (row 8) is still reading as F10+AI10-E10 disregarding the filter when it should be F8+AI8-E8. I was advised to use "Counter" and "String Replacer" transformers but since I'm not too familiar with FME, I'm not sure how to use a String Replacer to write a script to have those formula row number replaced correctly. Can someone please help me out? I'm attaching two excel files. One is the data I need (which is a simplified version...the original files is huge with lots of information and formulas). This file has two sheets 'original' and Poles_FME_output which should how the output should be but the row 8 formula is all messed up 'error' . and the other file is the category I use for filter 'just Code'.

Also attaching a JPEG of my FME workbench and jpeg of another example -- how the output (highlighted yellow) number looks different.

Thank you.

2 Attachments

2 replies

pallegama
Badge

@grishmash

As you were advised, it is possible to use "Counter" and "StringReplace" transformers combined with "AttributeExposer" transformer as depicted in the example.fmw attached.

The current row number is stored in the attribute "xlsx_row_id" in FME. Therefore, first you have to expose the attribute "xlsx_row_id".

Then re-calculation of row number can easily be done using a "Counter" setting the "Count start = 2", as row numbers in excel starts with 2 after leaving the first row for column headings.

Finally, the "StringReplacer" replaces the existing row numbers in formula with calculated row numbers.

I hope this helps you to solve the issue.

Thanks,

Priyantha

1
Badge +1

Hi @grishmash

I’ve had a look at the information you provided and I think the problem lies in the fact that the Excel formula are using direct references (lets say we have a dataset with 5 rows and the calculation C5 has ‘=A5-B5’ for example). When you read the data and review the formula (AttributeName.formula) you can see this.

As you are then filtering out some data records, some of the records are removed but the direct references on the remaining records are unchanged (lets say row 4 was removed, then C4 would have ‘=A5-B5’ from the above example).

So I think you need to update the references used in the formula, and from doing some reading it looks like using indirect references give you this – using indirect(), row(), column() functions you can build the formula in Excel to update based on the current row and column values. You can build new attributes based on formula in the same way within FME using an AttributeManager or similar, and the Excel writer will interpret them.

Have a look here:

https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/xlsx/using_excel_writer_tips.htm?

I’ve attached an example FME workspace with a few records that show direct and indirect references. It replicates your issue when one of the data records is removed – see calculated attribute ‘Bal’. But also provides a solution using indirect references – see calculated attribute ‘Out_indirect’.

Hopefully that helps you

Mary

Reply


Community Stats

28,650
Posts
109,267
Replies
36,523
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.