We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

JSON output transposal and nested grouping

epireve
5 - Atom

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;

Screen Shot 2018-04-25 at 08.47.31.png

 

 

And my workflow looks like this;

Screen Shot 2018-04-25 at 08.32.22.png

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.

3 REPLIES 3
epireve
5 - Atom

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
8 - Asteroid

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
5 - Atom

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

Labels