Group by Row Values

Question

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

JSON NameJSON String ValueID322FirstNameJohnLastNamePeterEmailemail@email.comNumber232343242ContactID45325ID34434FirstNameJoeLastNameLinLastNameDowEmailemail@email.comNumber4533ContactID4646546ID3545FirstNamePeterLastNameWhiteLastNameMullEmailemail@email.comNumber45435ContactID4534543

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 NameJSON String ValueGrouping IDID3221FirstNameJohn1LastNamePeter1Emailemail@email.com1Number2323432421ContactID453251ID344342FirstNameJoe2LastNameLin2LastNameDow2Emailemail@email.com2Number45332ContactID46465462ID35453FirstNamePeter3LastNameWhite3LastNameMull3Emailemail@email.com3Number454353ContactID45345433

Jean-Balteryx · Accepted Answer

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 Answer

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 Answer

Hi @mphillips27

Here's my solution.  Workflow attached.

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