Oracle Analytics and AI

user13550855

Hello All,

Can you please help me in achieving below scenario in OBIEE.

I have to compare current column in a row, with column in next row.

For Example: In below table 1st 3 rows is having same ID 100, I need to compare 1st row with 2nd if it is same then the expected column should show value 0 and if it is different just like if you compare 3rd and 4th row Expected column should display as 1. Please let me know if there is a way to get this.

ID	Expected
100	0
100	0
100	1
200	1
300	0
300	1

Thanks

Gianni Ceresa

Hi,

You can definitely do it, just think more "analytics" and less database.

Sure you can use EVALUATE and push that down in the database but then you will be strictly linked to your database with physical SQL in your analysis.

Ideally in your data you would have a flag generated by your ETL (or something else) to flag that kind of things.

If you really want to make that in OBIEE you can do something like that:

RSUM(1 by "your table"."ID") gives you a kind or rownumber for each ID, increasing by 1, so what you are interested in is the maximum of this value.

MAX(RSUM(1 by "your table"."ID") by "your table"."ID") gives you the maximum rownumber for each given ID.

Final step: a simple CASE WHEN...

CASE WHEN RSUM(...) = MAX(RSUM(...)...) THEN 1 ELSE 0 END

Done !