How to show each record with max data....

Question

Hi to everyone,

this is my scenario:

code    count(id)       description

122           2                 brontolo

133           1                 pluto

133           1                 paperino

133           1                 paperone

Request is to show, in case of same count id (in this case 1), ONLY 1 row for each code with max date.

this what happen if i add a new object with date:

code    count(id)       description               date

122           1                 pippo                10/02/2016

122           1                 brontolo            10/08/2016

133           1                 pluto                 09/04/2016

133           1                 paperino           08/03/2016

133           1                 paperone          10/02/2016

if i try to make a max aggregation like max(date by code), it looks like:

code    count(id)       description               date

122           2                 brontolo            10/08/2016       (OK)

133           1                 pluto                 10/02/2016       (DUPLICATED ROW)

133           1                 paperino           10/02/2016       (DUPLICATED ROW)

133           1                 paperone          10/02/2016       (DUPLICATED ROW)

this what i'd like to see as result:

122           2                 brontolo            10/08/2016       (OK)

133           1                 paperone          10/02/2016       (OK)

how can i filter all the report to allow this result?

i thought about a case when statement like:

case when rcount(description by code) > 1 then select description WHERE date = max(date by code) else descpription end

this what i really need, but of course is not possible to use this syntax inside a formula field in OBIEE.

Can you give me some idea?

Many thanks guys and thanks for your help.

Simone

Joel Acha · Answer

For your COUNT, you'll need something like this:

COUNT(id BY code)

For your date, you'll need something similar:

MAX(date(TIMESTAMP_CREAZIONE) BY code)

For the Description, do you want to return the value that corresponds to the MAX date(TIMESTAMP_CREAZIONE) for that code? It seems to me that the description and date are from a type 2 slowly changing dimension. Is that correct?

Barticchia · Answer

Ciao Christian and thanks for your reply!

i would to see just a distinct code with the count id and the description with the max data.

this is the result:

codecount(id)  description          date

122           2                 brontolo            10/08/2016       (OK)

133           3                 paperone          10/02/2016       (OK)

starting from these datas:

code    count(id)       description(JOBTYPE)      date(TIMESTAMP_CREAZIONE)

122           1                 pippo                                         10/02/2016

122           1                 brontolo                                     10/08/2016 (max data for code 122)

133           1                 pluto                                          09/04/2016

133           1                 paperino                                    08/03/2016

133           1                 paperone                                   10/02/2016 (max data for code 133)

otherwise i hit goal joking with the max function with group by:

MAX(FE_DETT_WR.TIMESTAMP_CREAZIONE by  FE_DETT_WR.JOBTYPE)

and than i filtered report using RANK function = 1 in this way:

RANK(max("FE_DETT_WR"."TIMESTAMP_CREAZIONE" by "FE_DETT_WR"."JOBTYPE") by "FE_DETT_WR"."LOCATIONCODE")

[Deleted User] · Answer

In your above example your 3 pluto/paperone/paperino share all other attributes and then what....take the "highest" one in an alphanumeric sort?!

You want OBI to select the first string by character sequence?