Can anyone suggest a way to use FME to find gaps in a daily time series?
I'm looking to generate a report for missing days of data in an effort to QC a dataset and know there must be a way to do this, right?
+18- Contributor
If you are using FME 2017 you can calculate the difference between two dates.
So, if you sort the data, you can compare the date of the current record with the date of the previous record (Using AttributeCreator as in the image):
Then use the DateTimeCalculator to calculate the difference.
If the difference is larger than 1 (Tester) you have a gap in the dates.
+4erik_jan wrote:
If you are using FME 2017 you can calculate the difference between two dates.
So, if you sort the data, you can compare the date of the current record with the date of the previous record (Using AttributeCreator as in the image):
Then use the DateTimeCalculator to calculate the difference.
If the difference is larger than 1 (Tester) you have a gap in the dates.
DATE01-01-201601-02-201601-03-201601-31-2016
I would create a new attribute, let's call it DATE_PREVIOUS_RECORD, grab the value from the previous record and populate it like this:
DATEDATE_PREVIOUS_RECORD01-01-2016<null>01-02-201601-01-201601-03-201601-02-201601-31-201601-03-2016
Then use the DateTimeCalculator to calculate a difference between DATE and DATE_PREVIOUS_RECORD, and if >1 then do something, right? I'm going to try this out now, thanks!
+1In FME 2017 take a look at the DateTimeCalculator transformer - it lets you add/subtract or calculate intervals, or construct an expression. It assumes the values are in FME format, so use the DateTimeConverter beforehand to convert your data to that format.
erik_jan wrote:
If you are using FME 2017 you can calculate the difference between two dates.
So, if you sort the data, you can compare the date of the current record with the date of the previous record (Using AttributeCreator as in the image):
Then use the DateTimeCalculator to calculate the difference.
If the difference is larger than 1 (Tester) you have a gap in the dates.
Alternatively, you can use FME Date/Time functions effectively, like this.
@DateTimeDiff(@DateTimeParse(@Value(DATE),%m-%d-%Y),@DateTimeParse(@Value(DATE_PREVIOUS_RECORD),%m-%d-%Y),days)
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.