How to implement group by in Dataweave based on first column in CSV

Question

I have an incoming CSV file that looks like this (notice that the first field is common - this is the order number)

36319602,100,12458,HARVEY NORMAN,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
36319602,101,12459,HARVEY NORMAN,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
36319602,102,12457,HARVEY NORMAN, 
36319601,110,12458,HARVEY NORMAN,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
36319601,111,12459,HARVEY NORMAN,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
36319601,112,12457,HARVEY NORMAN, 
36319603,110,12458,HARVEY NORMAN,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
36319603,121,12459,HARVEY NORMAN,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
36319603,132,12457,HARVEY NORMAN,

This is my current Dataweave code

list_of_orders: {
    order: payload map ((payload01 , indexOfPayload01) -> {
        order_dtl: 
        [{
            seq_nbr: payload01[1],
            route_nbr: payload01[2]
        }],
        order_hdr: {
            ord_nbr: payload01[0],
            company: payload01[3],
            city: payload01[4],
        }
    })
}

An example of the desired output would be something like this ... (this is just mocked up). Notice how I would like a single header grouped by the first column which is the order number - but with multiple detail lines


    "list_of_orders": {
      "order": [
        {
          "order_dtl": [
            {
                seq_nbr: 100,
                route_nbr: 12458
            },
            {
                seq_nbr: 101,
                route_nbr: 12459
            },
            {
                seq_nbr: 102,
                route_nbr: 12457
            }                        
          ],
          "order_hdr": 
          {
            ord_nbr: 36319602,
            company: HARVEY NORMAN
          }
        }
      ]
    }

It works fine except that it is repeating the order_hdr key. What they would like is a single header key with multiple details beneath. The grouping is to be based on "ord_nbr: payload01[0]"

Any help appreciated

Thanks

Can you provide an example of how you would like the output to be? It is not clear from your description — machaval, May 21 '19 at 11:31
Hi there - I have done this. I also corrected the sample incoming data as the city was not required. Thanks — Steven Mark Integration, May 21 '19 at 12:31

Ryan Carter · Accepted Answer · 2019-05-22T12:48:51.707

I think you're using Dataweave 1. In dw1, this groupBy gets the desired output(Note you can change the field pointers [0],1 etc to field name mappings if you have them set up as metadata etc):

%dw 1.0

%output application/json
---
list_of_orders:  {
    order: (payload groupBy ($[0])) map {
    order_dtl: $ map {
        seq_nbr: $[1],
        route_nbr: $[2]
    },
    order_hdr: 
          {
            ord_nbr:  $[0][0],
            company: $[0][3]
          }
 }}

UPDATE

Here is the output for the new input sample with multiple orders:

{
  "list_of_orders": {
    "order": [
      {
        "order_dtl": [
          {
            "seq_nbr": "110",
            "route_nbr": "12458"
          },
          {
            "seq_nbr": "121",
            "route_nbr": "12459"
          },
          {
            "seq_nbr": "132",
            "route_nbr": "12457"
          }
        ],
        "order_hdr": {
          "ord_nbr": "36319603",
          "company": "HARVEY NORMAN"
        }
      },
      {
        "order_dtl": [
          {
            "seq_nbr": "100",
            "route_nbr": "12458"
          },
          {
            "seq_nbr": "101",
            "route_nbr": "12459"
          },
          {
            "seq_nbr": "102",
            "route_nbr": "12457"
          }
        ],
        "order_hdr": {
          "ord_nbr": "36319602",
          "company": "HARVEY NORMAN"
        }
      },
      {
        "order_dtl": [
          {
            "seq_nbr": "110",
            "route_nbr": "12458"
          },
          {
            "seq_nbr": "111",
            "route_nbr": "12459"
          },
          {
            "seq_nbr": "112",
            "route_nbr": "12457"
          }
        ],
        "order_hdr": {
          "ord_nbr": "36319601",
          "company": "HARVEY NORMAN"
        }
      }
    ]
  }
}

Apologies - I should have provided better sample data. There is actually more than one ord_nbr - I have corrected the sample data. Thanks — Steven Mark Integration, May 22 '19 at 03:26
This works for multiple ord_nbr as well. updated the answer with the sample output it produces for the new input with multiple ord_nbr. Or are you expecting the result in some other format? — Ryan Carter, May 22 '19 at 12:49

How to implement group by in Dataweave based on first column in CSV

1 Answers1