Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
eduardo_dimperi
Valued Contributor II

Help with Max() and Min()

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

max.JPG

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;

1 Solution

Accepted Solutions

Re: Help with Max() and Min()

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;

11 Replies

Re: Help with Max() and Min()

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_dimperi
Valued Contributor II

Re: Help with Max() and Min()

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

Re: Help with Max() and Min()

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_dimperi
Valued Contributor II

Re: Help with Max() and Min()

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?

Re: Help with Max() and Min()

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_dimperi
Valued Contributor II

Re: Help with Max() and Min()

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

Re: Help with Max() and Min()

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?

Re: Help with Max() and Min()

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_dimperi
Valued Contributor II

Re: Help with Max() and Min()

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