Step 1 — Keep Only Successful Transactions

local!successfulTransactions: reject(

fn!isnull,

a!forEach(

items: ri!transactions,

expression: if(

fv!item.status = "SUCCESS",

fv!item,

null

)

)

)

a!forEach() iterates through every transaction and keeps it only when its status is "SUCCESS".

reject(fn!isnull, ...) removes the null values created for failed transactions.

For the given input, these transactions are removed:

John → 3000 CREDIT FAILED

Mike → 1000 CREDIT FAILED

So only successful transactions are used in the remaining calculations.

Step 2 — Get Unique Customers

local!customers: union(

index(

local!successfulTransactions,

"customer",

{}

),

index(

local!successfulTransactions,

"customer",

{}

)

)

index() extracts the customer field from all successful transactions.

union() removes duplicate customer names.

For the example, the resulting list is:

{ "John", "David", "Sarah", "Mike" }

Step 3 — Calculate Net Amount and Risk

local!results: a!forEach(

items: local!customers,

expression: {

customer: fv!item,

The expression now processes each unique customer individually.

Calculate Net Amount

netAmount: sum(

a!forEach(

items: index(

local!successfulTransactions,

wherecontains(

fv!item,

index(

local!successfulTransactions,

"customer",

{}

)

),

{}

),

expression: if(

fv!item.type = "CREDIT",

fv!item.amount,

-fv!item.amount

)

)

)

wherecontains() finds all successful transactions belonging to the current customer.

The if() determines whether the amount should be positive or negative:

CREDIT → +amount

DEBIT → -amount

For example, John has:

4000 CREDIT → +4000

12000 DEBIT → -12000

Therefore:

4000 - 12000 = -8000

The same calculation produces:

Assign Risk

risk: if(

sum(...) < -5000,

"HIGH",

if(

sum(...) <= 0,

"MEDIUM",

"LOW"

)

)

The calculated net amount is evaluated against the risk rules:

Net Amount < -5000 → HIGH

Net Amount <= 0 → MEDIUM

Net Amount > 0 → LOW

Therefore:

John → -8000 → HIGH

David → -2500 → MEDIUM

Sarah → 8000 → LOW

Mike → -16000 → HIGH

Step 4 — Keep Only HIGH and MEDIUM Risk

local!finalCustomers: reject(

fn!isnull,

a!forEach(

items: local!results,

expression: if(

contains(

{ "HIGH", "MEDIUM" },

tostring(fv!item.risk)

),

a!map(

customer: fv!item.customer,

netAmount: fv!item.netAmount,

risk: fv!item.risk

),

null

)

)

)

contains() checks whether the customer's risk is either "HIGH" or "MEDIUM".

Customers with "LOW" risk are converted to null and removed by reject().

The a!map() converts each remaining dictionary into a map so it can be used reliably with the subsequent sorting operation.

Sarah is therefore removed:

Sarah → 8000 → LOW → Excluded

Remaining data:

John → -8000 → HIGH

David → -2500 → MEDIUM

Mike → -16000 → HIGH

Step 5 — Sort by Risk and Net Amount

todatasubset(

local!finalCustomers,

a!pagingInfo(

startIndex: 1,

batchSize: -1,

sort: {

a!sortInfo(

field: "risk",

ascending: true()

),

a!sortInfo(

field: "netAmount",

ascending: true()

)

}

)

).data

todatasubset() applies the sorting rules defined in a!pagingInfo().

The first sorting condition is:

a!sortInfo(field: "risk", ascending: true())

This sorts the risk values alphabetically:

HIGH

HIGH

MEDIUM

The second condition:

a!sortInfo(field: "netAmount", ascending: true())

sorts customers with the same risk by their net amount.

The final result is:

{

{customer: "Mike", netAmount: -16000, risk: "HIGH"},

{customer: "John", netAmount: -8000, risk: "HIGH"},

{customer: "David", netAmount: -2500, risk: "MEDIUM"}

}

Overall Flow

All Transactions

↓

Step 1: Remove FAILED

↓

Step 2: Get Unique Customers

↓

Step 3: Calculate Net Amount

↓

Assign Risk

↓

Step 4: Remove LOW Risk

↓

Step 5: Sort by Risk + Net Amount

↓

Final Customer Results