I'm searching for the right formula or expression to use for lumping similar categories together using the Edit Column Formula box in OBIEE Answers. The column measure that captures this category is "Pending Claim - Suspense"."Suspense Reason" and the string of text associated with this measure can be one of hundreds of choices. I'm simply wanting to create an expression that COUNTS or SUMS certain categories.
For example. I want to COUNT all widgets that has the phrase "pending records" from the "Pending Claim - Suspense"."Suspense Reason" column measure. I may have to use wild card characters to account for other phrases. To my knowledge, this should be in a string format.
I'm a complete noob with database expressions/formulas so I'm not sure how many other questions I can answer. I'm thinking the expression will look something like: SUM(CASE WHEN "Pending Claim - Suspense"."Suspense Reason" LIKE 'pending records' THEN 1 ELSE 0 END). This logic may be completely off!
That exact formula didn't work, so I had to go with this:
SUM(CASE WHEN "Pending Claim - Suspense"."Suspense Reason" LIKE '%pending records%' THEN 1 END). However, this is returning 0 for everything, when I know there are thousands of widgets that includes the phrase "pending records"
Any other suggestions? Do I need to CAST or use CHAR for anything?
Interesting...all values are N. Like I said though, there are thousands of these phrases that would say "pending record" in it. Does it have to be in that exact order of the phrase, or will it target any and all uses of "pending record"?
I think the problem might be that it's searching for the exact phrase, rather than just those 2 words.