Question

How to reference a value in a previous row in an Excel Spreadsheet in FME and delete rows that are no longer needed.

J
Badge

I have to put through a translation an Excel document with, say, 60 elements. I have an attribute "Action", which specifies what kind of thing happened, a second attribute, "_Increase", which is an attribute that is applied to a third attribute, "PCI" (which varies based on year - year is, incidentally, another attribute used to keep track of when a specific action takes place), depending on the type of action and when the action happened. Let's say, for the first three years (in the first three rows), "Action" is "Do Nothing" and "_Increase" = 0, I send those features directly into the writer. In year 4, an action occurs, I apply "_Increase" to Year 4's "PCI" and every subsequent year's PCI value until I reach the end of the document. Any remaining rows after that need to be filtered to the writer until the next action occurs after any applicable calculations have been applied.

Is there a transformer that can be used to automate this process? Alternatively, is there a python script (e.g. using the "Python Caller" transformer) that can be used to carry out this process?

10 replies

todd_davis
Rank
Userlevel 1
Badge +12

Yes, both AttributeCreator and AttributeManager has "adjacent feature handling". So you can assign a value to a feature based of prior or subsequent features.

So for instance "PCI" could be based on "Increase" number from the current feature plus the PCI number of the previous feature

G
Badge +3

You can also expose the row id on the reader. Wich allows you great freedom.

(By using excell columnnames ,rather then fieldnames on the reader, you can even target any cell at will.)

There some good examples of this om the forum.

J
Badge

You can also expose the row id on the reader. Wich allows you great freedom.

(By using excell columnnames ,rather then fieldnames on the reader, you can even target any cell at will.)

There some good examples of this om the forum.

Please provide a link to some examples. What I'm hoping to do is perform a test (using Tester) to remove the first n number of rows from a spreadsheet where no increase/action exists and to apply an increase to every row, beginning in the first row where anything that isn't "No Action" is observed (e.g. from row 4, where [specified action] occurs to the final row of the table), then remove all of the rows beginning at row 4 and ending at the row that comes immediately before the next "Action" (e.g. from Row 4 to Row 7, where there is an Action in Row 8, presumably). The screenshot shown above is certainly helpful.

J
Badge

You can also expose the row id on the reader. Wich allows you great freedom.

(By using excell columnnames ,rather then fieldnames on the reader, you can even target any cell at will.)

There some good examples of this om the forum.

Please be aware that I am using Workbench 2015.1, if that helps.

G
Badge +3

using 2015 too (as wel as 2016)

I don't know the possibel values of The action nor the criteria.

But you might be able to solve it using variablesettter and reader.

Maybe you have a piece of sample data to play with?

M
Badge +5

It's very simple to test for Action = "Nothing" and ignore those features, and also to test for Action = "Increase" and increase the current feature's value by that amount.

What is more difficult is to carry that increase on to subsequent features.

In the past we would have suggested VariableSetter/VariableRetriever, but it's now better to use adjacent feature attributes.

For that reason I would go with @todd_davis solution, or a variation of that based on what I understand your need to be.

Basically, create a new attribute called RunningIncrease whose value is feature[-1].RunningIncrease+Increase. That number will tell you how much to add (or subtract) from the current PCI.

The other possible solution that I mention in passing, is to use an InlineQuerier transformer. Basically that allows you to run a sql query on tables generated from your data. So you could set up a query like PCI = PCI + ([Sum(Increases)] where ID < current ID) (that's not real SQL, just a pseudo query - my sql isn't very good, but I think this method could work).

M
Badge +5

It's very simple to test for Action = "Nothing" and ignore those features, and also to test for Action = "Increase" and increase the current feature's value by that amount.

What is more difficult is to carry that increase on to subsequent features.

In the past we would have suggested VariableSetter/VariableRetriever, but it's now better to use adjacent feature attributes.

For that reason I would go with @todd_davis solution, or a variation of that based on what I understand your need to be.

Basically, create a new attribute called RunningIncrease whose value is feature[-1].RunningIncrease+Increase. That number will tell you how much to add (or subtract) from the current PCI.

The other possible solution that I mention in passing, is to use an InlineQuerier transformer. Basically that allows you to run a sql query on tables generated from your data. So you could set up a query like PCI = PCI + ([Sum(Increases)] where ID < current ID) (that's not real SQL, just a pseudo query - my sql isn't very good, but I think this method could work).

For more info, check out chapter 6 (specifically 6.7) of the advanced FME training course. It covers the multiple/adjacent feature attribute methodology:

https://www.gitbook.com/book/safe-software/fme-desktop-advanced-training-2016/details

There is even an exercise to try out that isn't totally different to your scenario.

J
Badge

Yes, both AttributeCreator and AttributeManager has "adjacent feature handling". So you can assign a value to a feature based of prior or subsequent features.

So for instance "PCI" could be based on "Increase" number from the current feature plus the PCI number of the previous feature

Hi @todd_davis, @mark2catsafe: Let's assume I wanted to make a comparison between, say, the seventh feature in a data set with a previous feature within the same data set at an unknown location, provided a certain condition exists (e.g. Curve# = feature[Unknown Prior Feature #].Curve#), is there a way to work around that in Attribute Creator?

J
Badge

Yes, both AttributeCreator and AttributeManager has "adjacent feature handling". So you can assign a value to a feature based of prior or subsequent features.

So for instance "PCI" could be based on "Increase" number from the current feature plus the PCI number of the previous feature

The document that I am looking to process is read into FME as an Excel document. I will also be writing to another Excel document. There is a certain pattern that I would like to follow, comparable to the pattern shown in the screenshot attached to this comment (e.g. screenshot3.png). Another screenshot shows, algebraically, what is supposed to happen (e.g. when a change happens in the Adjusted PCI value (e.g. Year 16), find the original PCI value that is closest to the adjusted value (e.g. Year 9) and subtract that from the present Adjusted PCI before adding the subsequent PCI from the original PCI column (e.g. Year 10) to the difference for rows 5 and 6 (e.g. screenshot4.png).

Above describes what needs to happen to the PCI documents I'm working with at the moment. The results that I have been able to produce thus far appear as such: screenshot5.png. The Adjusted PCI value should not equal the existing PCI nor should the PCI equal 0 after Year 29. When a treatment occurs, what I mentioned previously with adjusting the PCI value should occur for every treatment year and year subsequent to it until another treatment occurs.

todd_davis
Rank
Userlevel 1
Badge +12

The document that I am looking to process is read into FME as an Excel document. I will also be writing to another Excel document. There is a certain pattern that I would like to follow, comparable to the pattern shown in the screenshot attached to this comment (e.g. screenshot3.png). Another screenshot shows, algebraically, what is supposed to happen (e.g. when a change happens in the Adjusted PCI value (e.g. Year 16), find the original PCI value that is closest to the adjusted value (e.g. Year 9) and subtract that from the present Adjusted PCI before adding the subsequent PCI from the original PCI column (e.g. Year 10) to the difference for rows 5 and 6 (e.g. screenshot4.png).

Above describes what needs to happen to the PCI documents I'm working with at the moment. The results that I have been able to produce thus far appear as such: screenshot5.png. The Adjusted PCI value should not equal the existing PCI nor should the PCI equal 0 after Year 29. When a treatment occurs, what I mentioned previously with adjusting the PCI value should occur for every treatment year and year subsequent to it until another treatment occurs.

@je6thoma Here is a workspace thrown together to achieve what is shown in Fig 3. I didn't think about it in great depth as I didn't have much time so it was very much in the moment and not planned out. You will note a "treatment" in year 16 and year 18 in the spreadsheet (e.g. raised values from the previous year). The output will show that Year 9 is the last year that had higher value than treatment Year 16, and Year 16 was the last year to have a higher value than treatment year 18. Be warned though, this has been made to perform the calculations on one "asset", so you will need to make it work across multiple assests for your purpose

pci.zip

Reply


Community Stats

28,688
Posts
109,425
Replies
36,543
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.