CODEWITHSUNDEEP
dataweavemule4
Vineetz
Vineetz

Reputation: 45

how do i place a sequential count or index based on a column value in mulesoft data weave?

i need to write a sequence/index in a column based on values in another column. Please find sample below to explain my request : Here i need to generate index values in the customindex column based on the values in color column.. so basically if there are 4 rows with red in column, then it should index the rows as 1,2,3,4 and then when its 2 rows with blue as a value, it should again index from 1 -> 1,2.

Input data:

[
  {
    "Type": "Header",
    "Color": "Red",
    "Customindex": ""
  },
  {
    "Type": "Header",
    "Color": "Blue",
    "Customindex": ""
  },
  {
    "Type": "LineItem",
    "Color": "Red",
    "Customindex": ""
  },
  {
    "Type": "LineItem",
    "Color": "Red",
    "Customindex": ""
  },
  {
    "Type": "LineItem",
    "Color": "Blue",
    "Customindex": ""
  },
  {
    "Type": "Header",
    "Color": "Yellow",
    "Customindex": ""
  },
  {
    "Type": "LineItem",
    "Color": "Yellow",
    "Customindex": ""
  }
]

here is the transformation that i need help with:

%dw 2.0
output application/json
var TM = flatten(payload map ((item, index) ->
[{
"Type":payload.type, //HEADER VALUES
"Color":"",
"Customindex":""
}]++
[{
"Type":payload.type,// LINE ITEM VALUES
"Color":payload.color,
"Customindex":index // need the index sequence to only count similar colors.. For ex : red start with 1,2,3 then for blue again 1,2 and so on
}]))
---
(((TM distinctBy $ ... filters and groupings

Sample data with details (THIS IS THE EXPECTED OUTPUT): sample table custom index is the expected column

Upvotes: 0

Views: 522

Answers (1)

user3078986
user3078986

Reputation:

Try this, there are comments added inline:

%dw 2.0
output application/csv

var data = [
  {
    "Type": "Header",
    "Customindex": "",
    "Color": "Red"
  },
  {
    "Type": "Header",
    "Color": "Blue",
    "Customindex": ""
  },
  {
    "Type": "LineItem",
    "Color": "Red",
    "Customindex": ""
  },
  {
    "Type": "LineItem",
    "Color": "Red",
    "Customindex": ""
  },
  {
    "Type": "LineItem",
    "Color": "Blue",
    "Customindex": ""
  },
  {
    "Type": "Header",
    "Color": "Yellow",
    "Customindex": ""
  },
  {
    "Type": "LineItem",
    "Color": "Yellow",
    "Customindex": ""
  }
]
filter ($.Type != "Header")

// Create a map that contains the value of color as the key and the current-index
// in the value.
var vs2idx = data.*Color distinctBy $ 
             reduce (e,acc={}) -> acc ++ {(e): 1}
             
// Set the header object to be used later on
var header = {Type: "Header",Color: "", Customindex: ""}

---
/*
 * The accumulator contain two fields:
 *  1. The env that contains the state that keeps the color counters
 *  2. The result that contains the rows with the Customindex value
 * This data structure keeps re-created for every single row--this is necessary
 * because data in dataweave are immutable--i.e. we can't make any in-place
 * updates.
 */
(data reduce (row,acc={env: vs2idx, result: []}) -> do {
    var r = acc.result + (row update {
        case s at .Customindex -> acc.env[row.Color]
    })
    var e = {(acc.env - row.Color), (row.Color): acc.env[row.Color]+1}
    ---
    {env: e, result: r}
}).result
// group by color
groupBy $.Color
// Get the values of the resulting object
pluck $
// Iterate over the values and inject the header
reduce (e,acc=[]) -> acc + header ++ e

Even though I am outputing the data in JSON they are easy to be transformed into CSV.

Here's the documentation of the features of DW I made use of:

  1. reduce
  2. Local variables
  3. Multi-value selector
  4. update operator

EDIT: Update the expression to generate CSV, without changing the position of the index and injecting the header rows.

Upvotes: 2

Related Questions