Question

Add new Row in Spreadsheet from Last Date to End of Year

  • 15 January 2014
  • 3 replies
  • 2 views
M
Badge
I have an excel spreadsheet that has values for a feature. There is a new row for each month. There is a value associated with each month which is why there is a new row.

 

 

The issue is that some of the values stopped being reported (some as long as 50 years ago.) 

 

 

I need to append new months from the last reported to current (well Dec 31, 2013 to be specific). For example, if the last reported date for value ABC was Dec 31, 2012, I need to add 12 new rows with ID# ABC and a date of Jan 2013, Feb, 2013, March 2013, etc... all the way up to Dec 2013. 

 

 

I am trying to find a way to iterativelly see the 'last' month for a unique value and add "30." 

 

 

I'm using/trying FME with this issue because there are thousands of unique records with varying degrees of last reported values and I would like to automate this process. If anyone has any ideas I would appreciate it.

 

 

Regards,

 

 

 

Matthew Brucker

3 replies

takashi
Rank
Userlevel 2
Badge +17
Hi Matthew,

 

 

The major issue is how to detect the last row in the spreadsheet? If so, one possible way is: 1) Add a Counter to append sequential number (e.g. "_count") to features.   2) Add an AttributeCreator to append an attribute (e.g. "_next_count") which indicates "_count" of the feature immediately after. Use "Multiple Feature Attribute Support" with settings like this.   Number of Subsequent Features: 1   If Attribute is Missing, Null or Empty: Use Other Value   Attribute Replacing Value: 0   _next_count = @Value(feature[+1]._count)   3) Add a Tester to determine whether the feature is the last row by testing if "_next_count" is less than "_count".   If the last row is not the last report, consider using a Sorter before the Counter in order to arrange the feature order by date ascending.

 

 

Takashi
M
Badge
Hi Takashi,

 

 

I think you're on to something and I was trying to figure using that approach out myself. 

 

 

https://www.dropbox.com/s/je13z4jls2tkkcq/2014-01-15_10-26-21.jpg

 

 

Basically I've gotten as far as being able to determine which features are "up-to-date" in that the last month available = 0. 

 

 

However, I'm still having issues trying to add new records if the last month > 0 (ie the last record was from years ago.) 

 

 

See the dropbox link above for an image of my workbench so far. 
takashi
Rank
Userlevel 2
Badge +17
Well then, how about copying the last row by the number of months using a Cloner? The Cloner appends "_copynum" attribute to every copied feature. Since "_copynum" contains 0-based sequential number I think you can then create month attribute of new rows based on "_copynum".

Reply


Community Stats

28,661
Posts
109,324
Replies
36,528
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.