Skip to main content
Solved

Finding entries in a spreadsheet where a value has increased


joe.fme
Contributor
Forum|alt.badge.img+4

Hi FME Community,

 

I've been sent a spreadsheet with a series of rent entries. The name of the property, the dates and rent are the fields which are important.

 

For each entry, which is a single line per rent payment, there may be an increase in the amount. This can be after 1 line, or 20. Basically, I need to only keep the entries which show an increase in rent amounts.

 

Trying to find these entries and automate them seemed simple at first - but has stumped me.

 

If anyone has any ideas, I'll be very grateful.

Thank you

Best answer by fhilding

fhilding wrote:

Hi Joe! I attached a workspace that uses attribute creator with adjacent features to solve how I interpreted your question. It looks for the previous row (after sorting the initial input), checks that it's the same property ID(or name, in your case) and if the rent_paid is greater than the previous months rent_paid. If so, it sents rent_increased to 1, otherwise it leaves it at 0. It's important to check for the property ID/names being the same too, otherwise you'll get edge effects when the property changes.

 

If you need to restore the original order, a counter before any of this stuff is a good idea. Let me know if I didn't get any aspect of your original question / input data correct, because there's more fun to be had with this stuff (VariableSetter / VariableRetriever especially!) : )

I couldn't upload the data in the same post, so... here's the data my example uses!

View original
Did this help you find an answer to your question?

4 replies

Forum|alt.badge.img+1
  • October 27, 2020

Hi Joe! I attached a workspace that uses attribute creator with adjacent features to solve how I interpreted your question. It looks for the previous row (after sorting the initial input), checks that it's the same property ID(or name, in your case) and if the rent_paid is greater than the previous months rent_paid. If so, it sents rent_increased to 1, otherwise it leaves it at 0. It's important to check for the property ID/names being the same too, otherwise you'll get edge effects when the property changes.

 

If you need to restore the original order, a counter before any of this stuff is a good idea. Let me know if I didn't get any aspect of your original question / input data correct, because there's more fun to be had with this stuff (VariableSetter / VariableRetriever especially!) : )


Forum|alt.badge.img+1
  • Best Answer
  • October 27, 2020
fhilding wrote:

Hi Joe! I attached a workspace that uses attribute creator with adjacent features to solve how I interpreted your question. It looks for the previous row (after sorting the initial input), checks that it's the same property ID(or name, in your case) and if the rent_paid is greater than the previous months rent_paid. If so, it sents rent_increased to 1, otherwise it leaves it at 0. It's important to check for the property ID/names being the same too, otherwise you'll get edge effects when the property changes.

 

If you need to restore the original order, a counter before any of this stuff is a good idea. Let me know if I didn't get any aspect of your original question / input data correct, because there's more fun to be had with this stuff (VariableSetter / VariableRetriever especially!) : )

I couldn't upload the data in the same post, so... here's the data my example uses!


joe.fme
Contributor
Forum|alt.badge.img+4
  • Author
  • Contributor
  • October 27, 2020
fhilding wrote:

I couldn't upload the data in the same post, so... here's the data my example uses!

Hi fhilding, thank you so much! I was going down the adjacent feature route and you....well you just hit the nail on the head.

You absolute star, thank you


Forum|alt.badge.img+1
  • October 27, 2020
fhilding wrote:

I couldn't upload the data in the same post, so... here's the data my example uses!

All good! Happy change-hunting :)


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings