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
Hi Marcus good morning, Still the same.
Can you please explain to me why group by doesn't group by date and for each date group filter the lowest value?
Hi Marcus, i think that works now and i understood the concept, i'll do more tests first and thanks for the help till now
I used the Marcus idea and change a little the code, now i understand the problem but dont know how to solve it.
If i dont use VALUE_READ in the group by i get no value and if i use i get more then one.
Any Ideia?
OUTPUT:
load
Floor(DATE_READ) as DATA,
Hour(DATE_READ) as HORA,
VALUE_READ,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA
RESIDENT CONCAT_TABLE
where DATE_READ>=TODAY()-2;
inner join (OUTPUT)
load
DATA,
HORA,
Min(VALUE_READ) as VALOR
resident OUTPUT
group by DATA,HORA;
DROP TABLE CONCAT_TABLE;
OUTPUT:
load
Floor(DATE_READ) as DATA,
Hour(DATE_READ) as HORA,
VALUE_READ,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA
RESIDENT CONCAT_TABLE
where DATE_READ>=TODAY()-2;
inner join (OUTPUT)
load
DATA,
HORA,
VALUE_READ,
Min(VALUE_READ) as VALOR
resident OUTPUT
group by DATA,HORA,VALUE_READ;
DROP TABLE CONCAT_TABLE;
I will try to simplify the approach from above and leave here the various transformations on the timestamp and the exact where-clause out:
Output:
load Date, Hour, Time, Value from Source where 1=1;
inner join (Output)
load Date, Hour, time(min(Time)) as Time
resident Output group by Date, Hour;
whereby the first part is a normal table-load (with some transformations and a where-clause). The second part loads the data from the first part grouped by Date and Hour so that this load will have max. 48 records (24 hours * 2 days) and min(Time) will return the min. Time for each of these day-hours-buckets. Aften this happens the join-statement which is an inner-join which meant that only those records that have a matching on all key-fields in both tables are left - an inner-join worked therefore like a where-clause and the conditions comes from an outside table-load.
Important by this approach are the (values within) the key-fields which are Date, Hour and Time - which you missed in your load-statement.
- Marcus
Hi Marcus, I didn't missed, but not worked for the first time or i dont quite understand.
and i still dont understand why my code doesn't work, if i have the same date, same hour and diferent values in VALUE_READ, why Min() doesn't work, what i missing?
Below your code adapted to my 2 variable DATE_READ(with contains the date of measures) and VALUE_READ(value read in the meter).
Again thank you for spend yout time to teaching me
Output:
load
Date(DATE_READ) AS DATA,
Hour(DATE_READ) AS HORA,
Time(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;
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
Hi Marcus,
I made some adjustments and finally it works.
Follow the final code and very thanks
LOAD
VALUE_READ,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,
Date(floor(DATE_READ)) AS DATA,
Time(frac(DATE_READ)) AS TEMPO
RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;
//************************************OK
Output:
load
Date(floor(DATE_READ)) AS DATA,
Hour(DATE_READ) AS HORA,
Time(frac(DATE_READ)) AS TEMPO,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,
VALUE_READ
resident CONCAT_TABLE
where DATE_READ>=TODAY()-2;
inner join (Output)
load
DATA,
HORA,
ID_LEITURA,
time(min(TEMPO)) as TEMPO
resident Output group by DATA, HORA,ID_LEITURA;
// //***************************************OK
NoConcatenate
VALOR:
LOAD
DATA,
HORA,
TEMPO,
ID_LEITURA,
Min(VALUE_READ) AS VALUE_READ
RESIDENT Output
Group By
DATA,
HORA,
TEMPO,
ID_LEITURA
;
Inner Join(TMP2)
Load
ID_LEITURA,
DATA,
VALUE_READ,
TEMPO
RESIDENT VALOR;
// // //**************************************
//DROP TABLE CONCAT_TABLE;
DROP TABLE Output;
DROP TABLE VALOR;