Question

Is there an easy way to filter the 6 oldest years of data and then export as separate .xls for each year, please?

H
Badge

Hello, I'm relatively new to FME so apologies if this is a simple question.

My data includes dates with the earliest being 2006 and the latest being 2019. My aim is to filter this to only include the oldest 6 years worth of dates (however, this is subject to change as the data is updated so it needs to be a fluid filter). I would like to further split this data into each separate year. I have tried using the fanout expression but I'm not entirely sure how to format this. When I tried this it also didn't seem to output all the dates per year. Is there a transformer that would be best for this task?

Thanks very much

10 replies

trevorm
Rank
Badge

Hello @hp127, I'm not an expert by any means but I think I can help with this;

Open FME > Generate > Select reader and writer

  1. Date field = year? or just date? (if Date and you need to clean it look at bottom picture)

     

    1. if year > use AttributeFilter
      1. select the year field > type the years you want to filter for
        1. Or you can select Import... for list > pick same table as reader and pick same field it will auto import. Great for 50 different options.

Reader and Writer

 

 

AttributeFilter Parameters and Import Wizard

 

Here is mine after it worked:

 

AttributeCreator if you need to clean up a date field so you only select Year (this is just a @Left)

trevorm
Rank
Badge

@hp127 Sorry I forgot to add how to Write to Excel in my answer:

Writer Excel, name it anything and select the correct save location

double click on the writer and name it the year you want "2006" > drag the AttributeFilter output for 2006 to it.

Right click on the Writer 2006 > Duplicate > double click on the new writer and type 2007 in name > drag 2007 from Filter to writer.

My sample 2005 naming

Attribute Filter to Writers:

 

 

mark2atsafe
Rank
Userlevel 4
Badge +25

That's a great question.

I think I'd be tempted to use the DateTimeCalculator to figure this out. Set the mode to Calculate Interval Between Datetimes. Select your incoming data attribute as the Start Datetime and under End Datetime open the text editor dialog and add @DateTimeNow() which is a function to calculate the current datetime. Set the Result Type (interval) to Years.

Now you know the difference between the original date and now. Add a Tester. If the difference is >6 then you can discard that record.

For the fanout, use the Fanout parameter in the writer parameters. The Fanout Directory is the folder you want to write to. For the Fanout Expression, open the text editor and in there use the year attribute plus the file extension .xlsx

I created a sample workspace. It should be a good starting point for you to figure out what to do in your own workspace. It only checks for 2 years (because of my data) so you'd need to change the Tester, and only outputs one dataset (again because of the data contents).

You can find it on Dropbox at: https://www.dropbox.com/s/jseimxlyr8uff4z/LastSixYears.fmwt?dl=0

Hope this helps

mark2atsafe
Rank
Userlevel 4
Badge +25

That's a great question.

I think I'd be tempted to use the DateTimeCalculator to figure this out. Set the mode to Calculate Interval Between Datetimes. Select your incoming data attribute as the Start Datetime and under End Datetime open the text editor dialog and add @DateTimeNow() which is a function to calculate the current datetime. Set the Result Type (interval) to Years.

Now you know the difference between the original date and now. Add a Tester. If the difference is >6 then you can discard that record.

For the fanout, use the Fanout parameter in the writer parameters. The Fanout Directory is the folder you want to write to. For the Fanout Expression, open the text editor and in there use the year attribute plus the file extension .xlsx

I created a sample workspace. It should be a good starting point for you to figure out what to do in your own workspace. It only checks for 2 years (because of my data) so you'd need to change the Tester, and only outputs one dataset (again because of the data contents).

You can find it on Dropbox at: https://www.dropbox.com/s/jseimxlyr8uff4z/LastSixYears.fmwt?dl=0

Hope this helps

Hmmm, Re-reading the question I think I've given you the six newest years, not the six oldest! There wouldn't be a lot of difference. You'd just need to find the oldest date (a StatisticsCalculator should help there) and adjust the DateTimeCalculator to find the difference between that and the attribute (instead of DateTimeNow and the attribute)

takashi
Rank
Userlevel 2
Badge +17

Assuming that the date values in the source Excel spreadsheet are stored with date data type, you can use the SubstringExtractor to extract year (first 4 digits in the date string) from the date value, then select only features in the oldest 6 years (between 2006 and 2011, i.e. older than 2012) with the Tester. Further, the year can also be used to configure feature type fanout in the destination feature type, as in:

takashi
Rank
Userlevel 2
Badge +17

Assuming that the date values in the source Excel spreadsheet are stored with date data type, you can use the SubstringExtractor to extract year (first 4 digits in the date string) from the date value, then select only features in the oldest 6 years (between 2006 and 2011, i.e. older than 2012) with the Tester. Further, the year can also be used to configure feature type fanout in the destination feature type, as in:

If actual values of the "oldest 6 years" in the source dataset could change whenever the workspace runs, this workflow might help you.

G
Badge +3

@hp127

 

tcl wrapped in fme datetime functions

@DateTimeDiff(YourDateField, @DateTimeNow(), years)<=6

H
Badge

Assuming that the date values in the source Excel spreadsheet are stored with date data type, you can use the SubstringExtractor to extract year (first 4 digits in the date string) from the date value, then select only features in the oldest 6 years (between 2006 and 2011, i.e. older than 2012) with the Tester. Further, the year can also be used to configure feature type fanout in the destination feature type, as in:

Thank @takashi for this detailed and perfect solution! Really grateful for your help!

H
Badge

That's a great question.

I think I'd be tempted to use the DateTimeCalculator to figure this out. Set the mode to Calculate Interval Between Datetimes. Select your incoming data attribute as the Start Datetime and under End Datetime open the text editor dialog and add @DateTimeNow() which is a function to calculate the current datetime. Set the Result Type (interval) to Years.

Now you know the difference between the original date and now. Add a Tester. If the difference is >6 then you can discard that record.

For the fanout, use the Fanout parameter in the writer parameters. The Fanout Directory is the folder you want to write to. For the Fanout Expression, open the text editor and in there use the year attribute plus the file extension .xlsx

I created a sample workspace. It should be a good starting point for you to figure out what to do in your own workspace. It only checks for 2 years (because of my data) so you'd need to change the Tester, and only outputs one dataset (again because of the data contents).

You can find it on Dropbox at: https://www.dropbox.com/s/jseimxlyr8uff4z/LastSixYears.fmwt?dl=0

Hope this helps

Thank you very much @mark2atsafe, you've helped me understand how to use the @DateTimeNow functions and Fanout parameters. FME is so clever, I'm so new to using it but think it's ace! :-)

H
Badge

Thank you so much for your help everyone, I'd hit a stumbling block but with all your advice I've been able to reach the required output! I really appreciate your input on this :-)

Reply


Community Stats

28,710
Posts
109,486
Replies
36,559
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.