Trouble with transaction amount increase formula

Question

Hello, I am relatively new to creating formulas on OAC and have been struggling to create a formula that will tell me when an individual gets approved for a transaction then eithers get declined or approved for a higher amount afterwards. I would want the time frame for when these transactions occur to be within 2 days. Example: Jonny comes in a gets approved or $100, he then decides to buy something for $500 directly afterwards or the next day. The attribute names I would want to use are Indv_Value, Tran_date, Tran_Amount, Tran_Count, Authorization_IND(Approval vs. Decline), and Transaction_ID. To recap, I am trying to get a way to show me when an Indv_Value gets approved then a subsequent transaction is attempted for a higher amount. Please let me know if something like this is even possible or if I should reach out elsewhere. Thanks so much!

RVohra · Answer

You need to be a SQL expert , may be  use a custom SQL query that joins the transaction table to itself, something like

SELECT ….

FROM transactions t1

JOIN transactions t2

ON t1.Indv_Value = t2.Indv_Value

AND t2.Tran_Date > t1.Tran_Date

AND t2.Tran_Date <= t1.Tran_Date + INTERVAL '2' DAY

WHERE t1.Authorization_IND = 'Approved'

AND t2.Tran_Amount > t1.Tran_Amount

Gianni Ceresa · Answer

Hi,

Welcome to the Oracle Analytics community and forum.

If you want to increase your chances, you should post a sample dataset of what you are working with, and then the expected output based on that sample dataset.

Because you are probably spending a number of hours on this thing and therefore your question does make perfectly sense in your mind, but nobody here knows what you are working with.

Also, just to be clear, are you working in a DV workbook? Or maybe you want those columns to be created in a dataset? Or somewhere else inside OAC?