Mule condense data based on a category

Question

Example below. I've got a set of account numbers, with an account attribute. For each account_number there are three categories, and I would like the sum for each account number based on each balance in DataWeave.

Data input

[
    {
        Account_Number: 1,
        Account: 5,
        Category: "A",
        Balance: 500
    },
    {
        Account_Number: 1,
        Account: 5,
        Category: "A",
        Balance: 700
    },
    {
        Account_Number: 1,
        Account: 5,
        Category: "B",
        Balance: 300
    },
    {
        Account_Number: 1,
        Account: 5,
        Category: "C",
        Balance: 100
    },
    {
        Account_Number: 2,
        Account: 10,
        Category: "B",
        Balance: 300
    },
    {
        Account_Number: 2,
        Account: 10,
        Category: "B",
        Balance: 800
    }
]

Data Output

[
    {
        Account_Number: 1,
        Account: 5,
        CategoryA_Balance: 1200,
        CategoryB_Balance: 300,
        CategoryC_Balance: 100
    }
    {
        Account_Number: 2,
        Account: 10,
        CategoryA_Balance: 0,
        CategoryB_Balance: 1100,
        CategoryC_Balance: 0
    }
]```

is it mandatory to give fields that have value 0 in output? – Anurag Sharma Feb 26 '22 at 15:29 — Anurag Sharma, Feb 26 '22 at 15:29

score 1 · Answer 1 · answered Feb 25 '22 at 23:23

I assume Categories are dynamic. If not, you can replace the Categories variable with a static array.

%dw 2.0
output application/json
var byAcctNbr = payload groupBy ($.Account_Number)
var categories = payload..Category distinctBy $
---
keysOf(byAcctNbr) map ((acctNbr) -> 
    do {
        var item = byAcctNbr[acctNbr] 
        var outItem = (item[0] default {}) - "Balance" - "Category"

        var balances = categories reduce ((category, acc={}) ->
            do {
                var accounts = item filter ($.Category == category)
                ---            
                acc ++ (
                        ("Category" ++ category ++ "_Balance"): if (isEmpty(accounts)) 0 
                                                                else sum (accounts.Balance)
                    )

        })
        ---
        outItem ++ balances
    }
)

`-` operator removes the given key/value from the object (the output doesn't contain the Balance and Category) — sudhish_s, Feb 28 '22 at 14:01

Karthik · Answer 2 · 2022-02-27T06:03:55.977

A Similar solution to sudhish. Breaking down the solution for better understanding

distinctBy Since .. will give you all the categories present in the input. DistinctBy will remove duplicates and you will have [A,B,C]

groupBy to group based over details of each account number

(item[0] - "Balance" - "Category") Since we require AccountNumber and Account only once so used item[0] and "-" to eliminate Balance and Category since we need to perform some conditional based logic further

pluck to convert the object with account number as key to array

map iterates over the details of each account number

map over the categories will yield you [A,B,C] for both the account numbers

filter to check if the Category present in the top level map matches the categories present in the variable. if (true) then sum(Balance) else 0

sum to add based on the categories matched using filter

%dw 2.0
output application/json
var categories = payload..Category distinctBy $
--- 
payload groupBy $.Account_Number pluck $ map(item,index)->{
    (item[0] - "Balance" - "Category"),
    (categories map (cat)->{
        ("Category" ++ cat ++ "_Balance"):
            if (isEmpty(item filter ($.Category == cat)))
                 0
            else
               sum((item filter ($.Category == cat)).Balance)
    })
}

Mule condense data based on a category

2 Answers2