Skip to main content
  • EN

Hi, I just jumped into FME world and get stuck with it. I have a data file of location based network and need to do analyzing with it. The file looks like this

UserID VenueID VnueCat VenueName Lat Long Offsetminmute UTC Time

470 49bbd 4bfds Arts & Crafts Store 40.719810 -74.0025 -240 Tue Apr 03 18:00:09 +0000

......

1. I need to convert the UTC into local time zone

2. How can I create a new dataset with specific UserID

3. How can I find any 2 different UserID which have same Lat, Long and LocalTime attributes and create a list of them come along with same attributes.

I have attached a small sample of the datafile below. Please let me know if you need full data file

1. You can use the DateTimeConverter transformer to convert the datetime format to standard FME format without timezone suffix (%Y%m%d%H%M%S).

DateTimeConverter Parameters

  • Input Format: * %b %d %H:%M:%S * %Y
  • Output Format: FME (auto format)

To get the local datetime value from the UTC datetime, set this expression to the Attribute Value column in an AttributeCreator or an AttributeManager. Assuming that an attribute called "_datetime" stores the UTC datetime.

@TimeZoneSet(@Value(_datetime)+00:00,local)

See these links to learn more about FME Date/Time format and functions.

2. You can use the Tester transformer to extract only features having a specific UserID.

1. You can use the DateTimeConverter transformer to convert the datetime format to standard FME format without timezone suffix (%Y%m%d%H%M%S).

DateTimeConverter Parameters

  • Input Format: * %b %d %H:%M:%S * %Y
  • Output Format: FME (auto format)

To get the local datetime value from the UTC datetime, set this expression to the Attribute Value column in an AttributeCreator or an AttributeManager. Assuming that an attribute called "_datetime" stores the UTC datetime.

@TimeZoneSet(@Value(_datetime)+00:00,local)

See these links to learn more about FME Date/Time format and functions.

2. You can use the Tester transformer to extract only features having a specific UserID.

3. The Aggregator with this setting groups records which have the same Lat, Long, and LocalTime, aggregates them into a single record, and creates a list that contains UserIDs for each group.

 

  • Group By: Lat, Long, LocalTime
  • Generate List: <checked>
  •  List Name: <set a list name>
  •  Add To List: Selected Attributes
  • Selected Attributes: UserID

 

Thank @takashi

I have succeed with 1 an 2, the question 3 after using Aggregator still had duplicated values, but I have removed and get the final result. I really appreciated for your support.

I have 1 more question. How can I create statistic tables, which show the UserID with its total check in and separate in specific VenueName

The result i wish would look like this:

UserID TotalCheckIn Arts & Crafts Store Home Beer ....

001 1000 100 100 100

Thank @takashi

I have succeed with 1 an 2, the question 3 after using Aggregator still had duplicated values, but I have removed and get the final result. I really appreciated for your support.

I have 1 more question. How can I create statistic tables, which show the UserID with its total check in and separate in specific VenueName

The result i wish would look like this:

UserID TotalCheckIn Arts & Crafts Store Home Beer ....

001 1000 100 100 100

This workflow might help you.

 

And here is a similar Q&A;: How to merge two data sets with connecting IDs, when some of the attributes need to be transposed?

 

This workflow might help you.

 

And here is a similar Q&A;: How to merge two data sets with connecting IDs, when some of the attributes need to be transposed?

 

Dear @takashi,

 

The BulkAttributeRenamer seems not to work. It kept same 3 attributes as the previous step, and the column "_count" display "missing" value

 

This workflow might help you.

 

And here is a similar Q&A;: How to merge two data sets with connecting IDs, when some of the attributes need to be transposed?

 

New attribute names ("Arts & Crafts Store", "Bridge", etc.) won't be exposed automatically by FME Workbench. See the Feature Information window in FME Data Inspector to check if the BulkAttributeRenamer worked as expected.

 

 

Reply


Community Stats

30,469
Posts
115,711
Replies
38,288
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.