Getting max date in a range

[eduardo_dimperio]

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?

[lironbaram]

because

if you try to run an aggregate function , you need to use group by statement

[rubenmarin · Accepted solution]

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;

[eduardo_dimperio]

Thank you Liron, you are right about it

[eduardo_dimperio]

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