Hi,

I have a XML as shown below

<events>
    <event> 
        <type>online</type>
        <event_date>1-Jan-21</event_date>
         <feedback_status>Closed</feedback_status>
    </event>
     <event> 
        <type>online</type>
        <event_date>1-Jan-20</event_date>
        <feedback_status>Closed</feedback_status>
    </event>
     <event> 
        <type>online</type>
        <event_date>1-Aug-21</event_date>
        <feedback_status>Open</feedback_status>
    </event>
    <event> 
        <type>offline</type>
        <event_date>1-Mar-21</event_date>
         <feedback_status>Closed</feedback_status>
    </event>
     <event> 
        <type>offline</type>
        <event_date>1-Feb-20</event_date>
        <feedback_status>Closed</feedback_status>
    </event>
</events>

I need to transform into JSON and derive values as given below

Here summary section has elements I need to derive based on the XML data

total_online_events = Count of events where type =”online”

is_any_online_open = Indicator set to True where type = “online” and feedback_status = “Open”

total_online_last_12_months = Count of events where type = “online” and event_date is within last 1 year

Could you please guide me how I could use StreamSets for this solution?

{
    "events": {
        "summary":{
            "total_online_events": 3,
            "total_offline_events": 2,
            "is_any_online_open": true,
            "is_any_offline_open": false,
            "total_online_last_12_months": 2,
            "total_offline_last_12_months": 1
        },
      "event":  
        {
          "type": "online",
          "event_date": "1-Jan-21",``
          "feedback_status": "Closed"
        },
        {
          "type": "online",
          "event_date": "1-Jan-20",
          "feedback_status": "Closed"
        },
        {
          "type": "online",
          "event_date": "1-Aug-21",
          "feedback_status": "Open"
        },
        {
          "type": "offline",
          "event_date": "1-Mar-21",
          "feedback_status": "Closed"
        },
        {
          "type": "offline",
          "event_date": "1-Feb-20",
          "feedback_status": "Closed"
        }
      ]
    }
  }