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?
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+22- Contributor
- 2179 replies
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.
+4If 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.
- 7843 replies
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)