Question

Aggregate by time but date issues

  • 11 September 2019
  • 1 reply
  • 3 views

Badge

Hi All,

 

We are trying to transpose some data and have run into a little issue

When we go to aggregate the data back, we get almost the correct data but we are aggregating by time so we get hourly data in columns broken down by hour in the following require format:

 

Times

 

 

Mon 01/01Tue 02/01Mon 08/0101:00-02:0043302:00-03:00333

 

The data is coming out exactly how we want however if we have duplicate days and we are using a group by on aggregation by time, it is missing where we have duplicate days like the above two Mondays in the concatenation on _attr_value as we cannot separate the times on the two Mondays.

We just end up with 5 comma separated values instead of 6 in the concatenation.

We followed this guide to transpose our row data to column data - https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html

Any ideas would be appreciated

Current example input

_attr_value time uid

98 22:00-23:00 17A

95 23:00-24:00 17A

94 05:00-06:00 17A

92 23:00-24:00 17A

91 05:00-06:00 17A

9 02:00-03:00 17A

85 05:00-06:00 17A

 

grouping by the time field and a uid.

 

CURRENT example output:

00:00-01:00,32,37,38,38,40

01:00-02:00,15,16,16,17,23

02:00-03:00,14,14,9,16,11

03:00-04:00,14,15,19,18,23

04:00-05:00,29,31,38,35,37

05:00-06:00,101,94,85,91,100

 

 


1 reply

Badge +3

@lemzip

If i get the question(?)

 

Use datetime as group by value. (instead of time)

Or select both time and date as groupby values.

 

Latter would be less work as you dont have to create datetimes of all the times and dates.

 

 

 

Reply