on 10-15-2015 8:42 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.