topic Script with max, min and total values in App Development

Script with max, min and total values

josemaria_cabre — Thu, 10 Oct 2024 11:06:55 GMT

Hi,

Let's imagine we load this data in our script:

LOAD * INLINE [
id, date, hour
a1, 01/01/2024, 8:00
a1, 01/01/2024, 9:00
a1, 01/01/2024, 10:24
a1, 01/01/2024, 11:56
a1, 02/01/2024, 7:34
a1, 02/01/2024, 9:00
a1, 02/01/2024, 10:24
a1, 02/01/2024, 14:55
b1, 02/01/2024, 1:02
b1, 02/01/2024, 3:33
b1, 02/01/2024, 4:44
b1, 02/01/2024, 6:25
];

And we'd like to get a summary resident table with 3 calculated fields on it, for each id and date: min date_hour, max_date_hour and total_time:

id	date	min_date_hour	max_date_hour	total_time
a1	01/01/2024	8:00	11:56	3:56
a1	02/01/2024	7:34	14:55	7:21
b1	02/01/2024	1:02	6:25	5:23

Is it possible to calculate these fields directly in the script? How would I acchieve that?

Thanks in advance for your help
Jose

Re: Script with max, min and total values

VBD — Thu, 10 Oct 2024 11:47:02 GMT

Hello,

You can try this,

TMP_TABLE:

LOAD * INLINE [

id, date, hour

a1, 01/01/2024, 8:00

a1, 01/01/2024, 9:00

a1, 01/01/2024, 10:24

a1, 01/01/2024, 11:56

a1, 02/01/2024, 7:34

a1, 02/01/2024, 9:00

a1, 02/01/2024, 10:24

a1, 02/01/2024, 14:55

b1, 02/01/2024, 1:02

b1, 02/01/2024, 3:33

b1, 02/01/2024, 4:44

b1, 02/01/2024, 6:25

];

TABLE:

Load

id,

date,

time(Max(hour)) as Max_Hour,

time(Min(hour)) as Min_Hour

Resident TMP_TABLE

group by id,date;

Drop table TMP_TABLE ;

exit script;

Regards,

Re: Script with max, min and total values

vuan — Thu, 10 Oct 2024 11:50:41 GMT

Hi,

you can try this

LOAD *, interval(max- min, 'hh:mm') as diff; LOAD id , date, interval(min(hour), 'hh:mm') as min, interval(max(hour), 'hh:mm') as max Group by id, date; LOAD * INLINE [ id, date, hour a1, 01/01/2024, 8:00 a1, 01/01/2024, 9:00 a1, 01/01/2024, 10:24 a1, 01/01/2024, 11:56 a1, 02/01/2024, 7:34 a1, 02/01/2024, 9:00 a1, 02/01/2024, 10:24 a1, 02/01/2024, 14:55 b1, 02/01/2024, 1:02 b1, 02/01/2024, 3:33 b1, 02/01/2024, 4:44 b1, 02/01/2024, 6:25 ];

Kind regards

Re: Script with max, min and total values

josemaria_cabre — Thu, 10 Oct 2024 12:11:24 GMT

Both solutions worked great,

thank you @vuan @VBD !!