Group By not working

[Polido]

Hello everyone,

I wonder what am I doing wrong with this group by that’s currently not working on my app.

map_1min_table_A:
LOAD
timestamp(Floor(DateTime,1/1440), 'YYYY-MM-DD hh:mm:ss') as DateTime_Map1,
VesselNo as VesselNo_Map1,
PropellerNo as PropellerNo_Map1,
maxstring(location) as location_Map1,
maxstring(peekLocation1min) as peeklocation_Map1
resident final_table
group by VesselNo, PropellerNo, DateTime
order by VesselNo, PropellerNo, DateTime;

The objective for this is to have one line per minute, vessel and propeller with the current location and location from 1 minute ago (this information is already available on the resident final_table).

Am I doing something wrong? The output of this generates more than 1 line per minute as you can see on the image attached.

Thanks in advance!

[Kushal_Chawda]

@Polido  Once you do the group by , do you need final_table? if not drop that table. If you need that table due to other fields then you need to perform inner join with group table like below. It will be joined on 5 highlighted fields. Make sure that field names are same in final_table

inner join (final_table)

LOAD
timestamp(Floor(DateTime,1/1440), 'YYYY-MM-DD hh:mm:ss') as DateTime_Map1,
VesselNo as VesselNo_Map1,
PropellerNo as PropellerNo_Map1,
maxstring(location) as location_Map1,
maxstring(peekLocation1min) as peeklocation_Map1
resident final_table
group by VesselNo, PropellerNo, DateTime
order by VesselNo, PropellerNo, DateTime;

[Polido]

Thank you for the fast reply Kushal!

I will try this but I'm afraid I'll have peeklocation for every second if I join them together.

the final_table has everything I want for most of the app and has data for every second. My aim was to create a separate table with only location and peeklocation for every minute, so the end user could select which frequency of data to see on the map. See the movement of the vehicle every second or every minute. Depending on the quantity of days selected.

[Kushal_Chawda · Accepted solution]

@Polido  I don't see anything wrong in your group by. probably you can try with left join and create flag. Use that flag to filter your data in set analysis

left join (final_table)

LOAD distinct
timestamp(Floor(DateTime,1/1440), 'YYYY-MM-DD hh:mm:ss') as DateTime_Map1,
VesselNo as VesselNo_Map1,
PropellerNo as PropellerNo_Map1,
maxstring(location) as location_Map1,
maxstring(peekLocation1min) as peeklocation_Map1,

1 as flag_check
resident final_table
group by VesselNo, PropellerNo, DateTime
order by VesselNo, PropellerNo, DateTime;

[Vegar · Accepted solution]

It could be that you are grouping by [DateTime] that is a timestamp on a more detailed level than minutes. [I assume this since you use Floor(DateTime,1/1440) in the load statement.]

To get one line per minute you also need to group by by the minute and not the original timestamp. 

Try to precreate your minute level timestamp field before doing your aggregation, or modify your "group by" to this: group by VesselNo, PropellerNo, Floor(DateTime,1/1440)

[Polido · Accepted solution]

Hi Vegar, thank you for your help!

Yes, this does the trick I've managed to get this working by doing the following group by and came here to close the topic.

group by VesselNo, PropellerNo, timestamp(Floor(DateTime,1/1440), 'YYYY-MM-DD hh:mm:ss')

Thanks for Kushal as well, could be a good workaround.