Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
Im trying to complete a field that show the current date load, not the current system date, but the the file.
So i did this:
ANALISE_TMP:
LOAD
ID_LEITURA,
NAME_SYSTEM,
NAME_GROUP,
NEIGHB_SYSTEM,
CITY_SYSTEM,
OID_METER,
NAME_TYPE_METER,
TYPE_CONSTANT,
NAME_UC,
ADDRESS_BLOCK_UC,
DATE_READ,
VALUE_READ,
Date(DATE_READ) AS HORA,
Max(Date(DATE_READ))AS Hora_referencia,
If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA0,
If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,
If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2
RESIDENT TMP2
ORDER BY OID_METER, DATE_READ DESC;
DROP TABLE TMP,TMP2;
Ocorreram os seguintes erros:
Invalid expression
O erro ocorreu aqui:
ANALISE_TMP: LOAD ID_LEITURA, NAME_SYSTEM, NAME_GROUP, NEIGHB_SYSTEM, CITY_SYSTEM, OID_METER, NAME_TYPE_METER, TYPE_CONSTANT, NAME_UC, ADDRESS_BLOCK_UC, DATE_READ, VALUE_READ, Date(DATE_READ) AS HORA, Max(Date(DATE_READ))AS Hora_referencia, If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA0, If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1, If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2 RESIDENT TMP2 ORDER BY OID_METER, DATE_READ DESC
Why can i use Date(DATE_READ) AS HORA but not Max(Date(DATE_READ))AS Hora_referencia?
By the way that some how to put this value in a variable and use in the grafic?
Hi Eduardo,
Max is aggregation function, and loads is record by record (only one date each time,so no reason for Max), to use an aggregation formula you should add a Group by clause by all the non-aggregated fields.
Or, in this case maybe you want to load Max Date in another indenpendent field, not related to any record in particular, in that case there are many possibilities, one of them using a resident load:
TableMaxDate:
LOAD Max(Date(DATE_READ))AS Hora_referencia
Resident ANALISE_TMP;
because
if you try to run an aggregate function , you need to use group by statement
Hi Eduardo,
Max is aggregation function, and loads is record by record (only one date each time,so no reason for Max), to use an aggregation formula you should add a Group by clause by all the non-aggregated fields.
Or, in this case maybe you want to load Max Date in another indenpendent field, not related to any record in particular, in that case there are many possibilities, one of them using a resident load:
TableMaxDate:
LOAD Max(Date(DATE_READ))AS Hora_referencia
Resident ANALISE_TMP;
Thank you Liron, you are right about it
Yeap,
When i read the liron's answer, i realized that will be better create an independent table with just that field because put some group by in my code mess with everything. So i did that:
GETDATE:
LOAD
MAX (DATE_READ)AS Hora_referencia
RESIDENT ANALISE_TMP;
and works very well