How to set hour ranges

[Shaglok]

Hello everybody.

I have a field called "entryTime" with the format 19/04/2023 17:23:51 and I need to group the data in 30 minutes groups (0:00-0:30, 0:30-1:00, etc, etc)

Any idea or help on how to do it. If possible, I prefer to not touch the load script and use formulas instead. I tried with 

aggr(if(entryTime<> '', interval(match(Ceil(Time#(entryTime, 'DD/MM/YYYY hh:mm:ss'))*48/24,'0:00', '23:59', '0:30')), ''), entryTime) but only have null results.

Thanks.

[stevedark]

Hi @Shaglok 

The load script is definitely the place to do it. You can calculate to display values at half hour granularity in the front end, but the aggr needs to work on a field and that will therefore cause many rows with the same value.

Ideally you would calculated the field entryTime30 inline as you bring the data in, but you could do it as a separate step after the main load if you have optmised QVD loads.

Something like:

TimeBuckets:
LOAD DISTINCT
    entryTime,
    Date(floor(Date#(entryTime, 'DD/MM/YYYY hh:mm:ss'), 1/24/2), 'DD/MM/YYYY hh:mm') as entryTime30
RESIDENT YourDataTable;

You should also ensure that you convert the entryTime field to a date time during the load script, or it will be much more resource heavy to sort values in the front end if they need to be converted from a string all the time.

Hope that helps.

Steve

https://www.quickintelligence.co.uk/blog/