cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Swapnil,

Thanks for your reply.

I am using Teradata as my backend.

I will get 24 values for one date, and out of them I will get only 3 or 2 or 1 distinct values.

So If I have 3 distinct values, I need to show all the 3 values, If I have only 2 distinct values I need to show 2 columns with distinct values and one column with null value. If I have only one distinct value, I need show one column with distinct values and 2 columns with null values.

Also,

I created a object in Universe which gives me the maximum value for that city. Can you guide me on how to create another 2 objects for 2nd highest and 3 rd highest values. if I have only one distinct value then 2nd highest and 3rd highest value should be null.

Thanks,

Ram

Former Member
0 Kudos

Hi, which are the objects in the report ? Besides,  can you share some sample data in an Excel file ?

Regards,

Rogerio

Former Member
0 Kudos

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

Answers (0)