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

Barticchia

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

[Deleted User]

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?

Barticchia

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:

code

count(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")

Joel

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?