Group by Row Values

[mphillips27]

I have a data set that is receiving JSON Name and JSON String Values from an API that looks like the following:

JSON Name	JSON String Value
ID	322
FirstName	John
LastName	Peter
Email	email@email.com
Number	232343242
ContactID	45325
ID	34434
FirstName	Joe
LastName	Lin
LastName	Dow
Email	email@email.com
Number	4533
ContactID	4646546
ID	3545
FirstName	Peter
LastName	White
LastName	Mull
Email	email@email.com
Number	45435
ContactID	4534543

I want to group each json object so i can do text to columns grouping by the ID. Sometimes the number of rows per json object is inconsistent, for example one json object could have 9 rows, some 10, however every new json object starts with the row value "ID". Is it possible to group all of the values within that "ID" span of rows. Desired output would be:

JSON Name	JSON String Value	Grouping ID
ID	322	1
FirstName	John	1
LastName	Peter	1
Email	email@email.com	1
Number	232343242	1
ContactID	45325	1
ID	34434	2
FirstName	Joe	2
LastName	Lin	2
LastName	Dow	2
Email	email@email.com	2
Number	4533	2
ContactID	4646546	2
ID	3545	3
FirstName	Peter	3
LastName	White	3
LastName	Mull	3
Email	email@email.com	3
Number	45435	3
ContactID	4534543	3

[Jean-Balteryx · Accepted solution]

Hi @mphillips27 ,

You can do this using a Multi-Row Formula that adds 1 to the Grouping ID each time the JSON Name value is "ID" !

Workflow attached. Tell me if it suits your need !

[atcodedog05 · Accepted solution]

Hi @mphillips27 

Use multi-row formula like below. To strat new block when Json Name = "ID"

IF [JSON Name]="ID" THEN [Row-1:Grouping ID]+1 ELSE [Row-1:Grouping ID] ENDIF

https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

Hope this helps : )

[HomesickSurfer · Accepted solution]

Hi @mphillips27 

Here's my solution.  Workflow attached.

I've also added a downstream variation that concatenates the multiple JSON values...

[mphillips27]

All of these worked, thank you!

[atcodedog05]

Happy to help : ) @mphillips27 

Cheers and have a nice day!

[Jean-Balteryx]

You are welcome @mphillips27! 😊