Categories
- All Categories
- 127 Oracle Analytics News
- 23 Oracle Analytics Videos
- 14.5K Oracle Analytics Forums
- 5.5K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 47 Oracle Analytics Trainings
- 7 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 8 Oracle Analytics Industry
- Find Partners
- For Partners
Applying filter in dimension column formula
Hello,
I am trying to achieve following logic from two tables in a single subject area. The goal is to have column restrict data using filter in a column formula involving 2 tables from the same subject area. Obviously, I tried using below syntax but in vain as expected throwing syntax error. Any other approach is very much appreciated.
case when "table1"."column1"='A' then "table2"."column2" LIKE '%~ABC' else "table2"."column2" end
Thanks.
Answers
-
Hi,
Obviously you have a syntax error as your CASE WHEN doesn't really mean anything ...
CASE WHEN <condition> THEN <value> ELSE <value> END
So what do you expect from your "table2."column" LIKE '%~ABC' as <value> after the THEN ?
The <condition> can be as complex as you want using multiple columns like: "table1"."column1" = 'A' AND "table2"."column2" = 'B'
So review your CASE WHEN and place the right formulas for <condition> and (the issue you have now) <value>
0 -
Hi,
Are you trying to say that when there's a specific subset of a column ('A') then you want to filter a second column based on a certain subset as well?
try something like this
case when "table1"."column1"='A' then filter("table2"."column2" using ("table2"."column2" like %~ABC')) else "table2"."column2" end
?
0 -
Dan - When I use your logic, I get an error because I don't have any measure columns in discussion: Function FILTER requires at least one measure column in its first argument. (HY000). Both the tables are dimension tables in same subject area.
So, here's what I am trying to achieve:
When "table1"."column1"='A' then I should be able to filter data in Column 2 that ends with '~ABC', hence I do not want to use = operator. Instead, I am using LIKE '%~ABC'. I am not sure if OBIEE understands an operator in THEN clause like the way I tried initially @Gianni Ceresa, my explanation below per what you said:
CASE WHEN <condition> THEN <value> ELSE <value> END
case when "table1"."column1"='A' then "table2"."column2" LIKE '%~ABC' else "table2"."column2" end
Please advise.
0 -
Ok, I guess I see what you try to do and you try to do it in the wrong place ....
You can't filter your data in a column formula, because the column formula isn't a filter and you have a real filter area in the criteria tab of your analysis.
So first of all you must add this as a filter if you want it to work.
What I understand is:
- if column1 = A then you want to see only data of column2 like %~ABC
- if column1 <> A then you don't really care about the value of column2 and so you want all the data
right?
If yes then add the 3 conditions to your analysis :
(column1 = A AND column2 like '%~ABC) OR column1 <> A
If it isn't right just tell use what must happen when column1 isn't A ....
0 -
Thanks @Gianni Ceresa. I think it works. Let me test in higher environments with better data. I will close this as answered then if it works.
0
