Skip to Content
avatar image
Former Member

Distinct values in a day for each hour In Webi or Universe

Hi,

I have the below requirement.

I have a date field with date time format. I will get the one value per hour for 3 of the cities and rest of the cities I will get only one value for entire day.

the 3 cities which get 24 values per day are around 3 distinct values. so I need to show only one row with 3 different columns.

Suppose I run a report from 01/01/2015 to 01/31/2015, the report should looks like the attachment.

So as per the attachment, if i get the 3 distinct values in 24 hrs I need show them in 3 columns, If I get only 2 distinct values in 24 hrs I need show them in 2 columns and 3rd column will be empty. If I have only distinct value in 24 hours I need to show only first column then 2nd and 3rd columns will be empty.

finally I need to show one row for one day.

Please suggest me.

Thanks In advance,

Ram

Forum.PNG (4.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Oct 18, 2015 at 09:25 PM

    Hi

    Can you please provide your backend database & table structure?

    What I think is you can apply logic on universe or IDT level by applying derived table.

    You can create query like

    select result2.city,

    case when  result2.ranking = 1 then ranking2.value end,

    case when  result2.ranking = 2 then ranking2.value end,

    case when  result2.ranking = 3 then ranking2.value end

    from

    (select Result1.city,

    value

    rank() over (order by value ) ranking from

    (select distinct city,

    value

    from table

    where date between @prompt('date1','D',,,,) and @prompt('date2','D',,,,)

    group by city)Result1

    )result2

    This syntax will work for Oracle.

    Thanks,

    Swapnil

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      You have right to create derived table on universe?

      If yes then you can try my solution as mentioned in my previous post.

      I think doing this by derived table is the best solution.

      Thanks,

      Swapnil