Help with Max() and Min()

[eduardo_dimperio]

Hi everyone, Happy new year (delayed)

Im having some complication using max() and min(). I understand the reason of my problem, but i need some help to find an answer,

Well, I need to get the consume of energy in a month, its quite simple just pick the first measure and the last measure of a meter and do one less the other.

The problem are, max() and min() aparently works analising the intire row, for that reason i get more then one measure for meter.

How can i solve that?

i.e

meter = 36075

i just want from output VAL_MAX = 120062 and DATA_MAX=09/12/16 04 00 05, but i get more then that.

Follow the code:

CONSUMO_MAX:

LOAD

    OID_METER,

    MAX(DATE_READ)  AS DATA_MAX,

    VALUE_READ AS VAL_MAX

    RESIDENT TMP2

    GROUP BY OID_METER,VALUE_READ

    ORDER BY OID_METER,VALUE_READ DESC;

[sunny_talwar · Accepted solution]

May be you need this:

CONSUMO_MAX:

LOAD

    OID_METER,

    MAX(DATE_READ)  AS DATA_MAX,

    FirstSortedValue(VALUE_READ, -DATE_READ) as VAL_MAX

Resident TMP2

Group By OID_METER;

[eduardo_dimperio]

Hey Sunny,

I found a way to solve this problem and yes your code works fine too!

Thank you so much

by the way follow the code that i wrote to solve, just for curiosity

CONSUMO:

LOAD

    OID_METER,

    MAX(DATE_READ)  AS DATA_MAX

    RESIDENT TMP2

    GROUP BY OID_METER

    ORDER BY OID_METER DESC;

inner join(CONSUMO)

LOAD

    OID_METER,

    DATE_READ AS DATA_MAX,

    VALUE_READ AS VAL_MAX

    RESIDENT TMP2

    ORDER BY OID_METER,VALUE_READ DESC;

I think your code is better, cause select just once

[sunny_talwar]

Yes, that's the other way to do it, there are probably few others way to do it, but not entirely sure which is the best way. If you got what you wanted, I would suggest closing this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[eduardo_dimperio]

Sunny,

I read a litle about FirstSortedValue() to understand better, then i chance a lithe the code.

CONSUMO_MAX:

LOAD

    OID_METER,

    DATE_READ,

    FirstSortedValue(VALUE_READ, -DATE_READ) as VAL_MAX

Resident TMP2

GROUP BY OID_METER,DATE_READ

Order By OID_METER,DATE_READ;

but i get again multiple values, so i ask you, this code above should not get the fisrt value or max value of all dates and output me one single value?

Im a little confuse about that, can you explain to me?

[sunny_talwar]

This my friend:

CONSUMO_MAX:

LOAD

    OID_METER,

    Max(DATE_READ) as DATA_MAX,

    FirstSortedValue(VALUE_READ, -DATE_READ) as VAL_MAX

Resident TMP2

GROUP BY OID_METER;

[eduardo_dimperio]

Yeah, but why i need to get the first value of a max(date_read) and just not first value of date_read?

whats the diference? In my head, says - "well, first value of a date must be the same no matter if is max(date_read) or date_read.

This is drive me crazy

and thank you for the pacience

[sunny_talwar]

If you group by DATE_READ, you will essentially have all the dates and FirstSortedValue() will end up showing all DATE_READ and there subsequent values. You need to group by a field which will end up having just one row for each OID_METER.

Does it make sense?

[sunny_talwar]

You can do this, if you don't want max date

CONSUMO_MAX:

LOAD

    OID_METER,

    Max(DATE_READ) as DATA_MAX,

    FirstSortedValue(VALUE_READ, -DATE_READ) as VAL_MAX

Resident TMP2

GROUP BY OID_METER;

But you cannot group by DATE_READ

[eduardo_dimperio]

hummm, more or less.

If i have for example this dates and values: 11/12 -100. 12/12 -200, 13/12 -300 and 14/12 - 400, when i group by date_read i get all this dates, so if i do firstvalue() of that, it thought that will bring me just only  the value of the first date. Returning me 14/12 if i order by date_read desc or 11/12 if i order by date_read asc.

But this not occur.

Maybe i need to study the concept of group by a little more