Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I have a litle problem here. I have some values by hour and i need to get the lower hour (by hour)
Like
Input
1:00 am --- 100
1:03 am --- 120
1:50 am --- 190
2:02 am --- 200
2:27 am --- 230
2:45 am --- 280
output
1:00 am --- 100
2:02 am --- 200
I have this code:
LOAD
DATE_READ,
VALUE_READ,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA
RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;
And tried this one, withou sucess.
LOAD
DATE_READ,
VALUE_READ,
MIN(DATE_READ) AS MENOR_VALOR
RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2
GROUP BY DATE_READ,VALUE_READ;
Someone could help me?
Thanks
Without the use of the floor/frac-function remain these fields timestamps like 42767,5422128472 for 01.02.2017 13:00:47 and date/time will just format it. Therefore try this:
Output:
load
Date(floor(DATE_READ)) AS DATA,
Hour(DATE_READ) AS HORA,
Time(frac(DATE_READ)) AS TEMPO,
VALUE_READ resident CONCAT_TABLE
where DATE_READ>=TODAY()-2;
inner join (Output)
load
DATA,
HORA,
time(min(TEMPO)) as TEMPO
resident Output group by DATA, HORA;
DROP TABLE CONCAT_TABLE;
Exit Script;
- Marcus
What is the error you get when you use the script?
LOAD
DATE_READ,
VALUE_READ,
MIN(DATE_READ) AS MENOR_VALOR
RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2
GROUP BY DATE_READ,VALUE_READ;
You could try something like this:
Output:
load
date(floor(DATE_READ)) as Date, time(frac(DATE_READ)) as Time,
hour(frac(DATE_READ)) as Hour, VALUE_READ
RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;
inner join (Output)
load Date, Hour, min(VALUE_READ) as MinVALUE_READ
resident Output group by Date, Hour;
- Marcus
Hi Sunny!
I have no error message, but the output is the same as input. I thought the output would be (in that print) only 00:06:29
Hi Marcus,
Thanks for your time !
I need the min Date group by hour and not value read. Sometimes, my measure 1:00 could be higher that 1:05
Look at Marcus's response below... I think that should resolve your issue
Then try it with some slight adjustements, something like this:
Output:
load
date(floor(DATE_READ)) as Date, time(frac(DATE_READ)) as Time,
hour(frac(DATE_READ)) as Hour, VALUE_READ
RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;
inner join (Output)
load Date, Hour, min(Time) as MinTime
resident Output group by Date, Hour;
- Marcus
Hi Marcus,
Unfortunately not worked, so i tried this code:
Input:
load
Min(VALUE_READ),
FLOOR(DATE_READ,0.1)
// ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,
RESIDENT CONCAT_TABLE
where DATE_READ>=TODAY()-2
group by DATE_READ;
with this result
And i dont understant why min(value_read), doesn't get only the small value per date
I did another code, more clean i think
Input:
load
VALUE_READ,
FLOOR(DATE_READ,000.1) AS DATA
RESIDENT CONCAT_TABLE
where DATE_READ>=TODAY()-2;
Output:
load
DISTINCT
MIN(VALUE_READ),
DATA
RESIDENT Input
group by DATA;
DROP TABLE Input;
I thought that my code would group by date and for each one pick the smallest value, but in the end it just picked the smallest value of all dates and replied
There are another slight adjustments necessary regarding to the format and the field-name of the min(Time):
Output:
load
today() as Date, time(time#(subfield(Time, ' ', 1), 'hh:mm')) as Time,
hour(time#(subfield(Time, ' ', 1), 'hh:mm')) as Hour, Value, Time as TimeOrigin
inline [
Time, Value
1:00 am, 100
1:03 am, 120
1:50 am, 190
2:02 am, 200
2:27 am, 230
2:45 am, 280
];
inner join (Output)
load Date, Hour, time(min(Time)) as Time
resident Output group by Date, Hour;
The inline-statement is only to have data for it and you won't need it in your load - only the methods to split the timestamp per frac() and floor() and then formatting them (and use only a pure floor() without a second parameter like 0.1 which won't create a date else just round the timestamp).
- Marcus