Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 60 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
OBIEE multiple row values in one row
I am trying to find out a solution where I can get the next three values of a column and add them in another field within the same row. So I found the LISTAGG function but its not working for me. Here is the function that I am using:
CAST(EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"Measures"."Strm",', ',) as char(16))
This is what I get:
I dont know how I would be able to get the next three "STRM" values to show up as follows:
BTW, the "Strm" will be entered through a report prompt so lets say if the User inputs "2336" then I will be needing the next two terms which in this case will be 2354 and 2366. I was thinking to use Presentation variables to pass the prompt values to the report.
Answers
-
You want the values from different rows to become distinct columns?
Well the most intelligent solution is to ETL this data into the correct form because otherwise this will be a very heavy operation that gets executed all the time.
0 -
The values are from the same column and this is how it looks in a table:
0 -
May be try this. First get a unique rank by row_number db function then do a pivot view. I have used different sample set Start Day Name (in your case emplid) ,Start Date (Strm)
In pivot view pull Strm as measure and set aggregation rule as Min (or Max).
0 -
Thank you and it works
It looks like:
Last thing I was looking for is to get the First 3 STRM's and also have to remove Nulls. I was trying out the LISTAGG but it doesnt work well with ROW_NUMBER already there... Please let me know should you have some solution?
0 -
If you are looking for only 3 then you need to filter on column which has row number (row_number <=3)
0 -
I tried but it doesnt work and here is the filter:
I get the following error:
ORA-30483: window functions are not allowed here at OCI call OCIStmtExecute. (HY000)
0 -
Basically why I didn't participate in the thread anymore and will just re-iterate my statement: this best goes into the physical design.
0
