nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Finding Duplicate values

User_DARJB

Hi all… Apologies in advance if this question is entered into the wrong forum. If so, please let me know where it would be best to ask this question. I am attempting to write an expression filter.

Essentially, what I would like to find are duplicate dollar amounts found in a variety of transactions. For example; A customer attempts a transaction for $512.44 using whatever credit card number they have.

However, the transaction may be declined multiple times before it gets approved. However, it may be they then attempt to use a different credit card number to get that amount of $512.44 approved.

I would like to write an expression filter that shows me duplicate transactions (2 or more) of a certain dollar amount like $512.44. I have attempted the following statement below but this does not appear to work:

case when COUNT(DISTINCT transaction_amount) >= 2 then 'suspicious transaction' else 'normal transaction' END

Any and all feedback would be greatly appreciated. Thank you all so much!

Find more posts tagged with

Analytics

SQL

Analysis and Data Visualization

Customizing Reports

Oracle Analytics Cloud

Accepted answers

Sushant Mishra

You could try creating a measure such as:
COUNT(transaction_id BY transaction_amount)

Then use it within a filter or a CASE expression like this:
CASE WHEN COUNT (transaction_id BY transaction_amount) >=2

THEN

'suspicious transactions'

ELSE

'normal transaction'

END

This approach should help in flagging amounts that appear multiple times. Of course, the exact implementation may vary depending on the structure of your dataset and how your transactions are modeled.

All comments

Sushant Mishra

You could try creating a measure such as:
COUNT(transaction_id BY transaction_amount)

Then use it within a filter or a CASE expression like this:
CASE WHEN COUNT (transaction_id BY transaction_amount) >=2

THEN

'suspicious transactions'

ELSE

'normal transaction'

END

User_DARJB

Sushant you are genius, thank you so much! I was able to implement this with no issue, the only thing I had to change was taking out "COUNT" in front of "CASE WHEN" when I was creating the expression. I appreciate your help!

Sushant Mishra

Happy to Help.