Skip to main content
  • EN

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 😎 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.

@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

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

30,092
Posts
114,247
Replies
37,720
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.