JSON output transposal and nested grouping

[epireve]

Dear community,

I started using Alteryx two days ago. Coming from Python programming background I could not believe how amazing and fast Alteryx could be for data preparation. Well done!

Right now I am trying to group records based on its date and title in JSON form. I used JSON build and transpose by ID’d each record as well as its date and title (using Multi-Row Formula). Post-transposal, I rewrote the "Date" value to classify child data. The challenges that I encountered currently is some records only shown one data elements and other groups did not group properly. I personally think the flow that I wrote might not the correct way to achieve this.

The overall data might be a bit complex but this is the output sample that I would like to see;

{
  "Dynamic": [
    {
      "Cards": [
        {
          "Date": "Jan-17",
          "Title": "Loss of PC",
          "Elements": [
            {
              "desc": "Aliquam luctus porttitor turpis curae nulla",
	      "incidentDate": "2017-01-25",
              "incidentType": "Medium"
            }
          ]
        },
        {
          "Date": "Feb-17",
          "Title": "Loss of PC",
          "Elements": [
            {
              "desc": "Ipsum nulla cursus nunc donec senectus ipsum erat ante id",
	      "incidentDate": "2017-02-02",
              "incidentType": "Medium"
            },
            {
              "desc": "Nam sollicitudin vel etiam fermentum duis dui ac adipiscing",
	      "incidentDate": "2017-02-13”,
              "incidentType": "Medium"
            },
            {
              "desc": "Felis in placerat habitant iaculis",
	      "incidentDate": "2017-02-22”,
              "incidentType": "Medium"
            },
            {
              "desc": "Eros nibh dictum laoreet lorem etiam quis malesuada",
	      "incidentDate": "2017-02-23”,
              "incidentType": "Medium"
            }
          ]
        }
      ]
    }
  ]
}

The original data look like this;

And my workflow looks like this;

Any other workaround is welcome to grouping JSON output. Here is the workflow file with samples. Thank you for reading my first post! Very much appreciate it.

Firdaus A.

[epireve · Accepted solution]

I have found a workaround to meet the wanted output by summarizing records with the groupBy action. The primary reason why not all records transposed as it should be but it returns redundant values and clash to each other.

After the groupBy, no more clashed and the output was fabulously working!

[kaianderson]

If anyone wants a better understanding of how the JSON Build tool works, I found this extremely helpful in formatting the "name" field to get nested properties in my JSON object.

[Darrie]

This was beautiful in it's simplicity.  Thank you!