PARSING COMPLEX JSON WITH DUPLICATE IDENTIFIERS - SOS318

[giulianosua1]

Hi Teams, 

First of all thank y'all for your participation on this community - it has really saved me from a lot of headaches. 

Here is my issue, I am downloading data from an API with over 27000 rows, I am able to parse it until the point that I have two columns: Name & Value. 

Now the issue that I am having is that my data uses the same identifier multiple times for different data sets. In other words, I have a list of data like the below with over 2000 different IDs. 

NAME	VALUE
ID	55555X
NAME	JOHN
LAST NAME	SMITH
NATIONALITY	FRENCH
ID	55555X
ADDRESS	10 RUE STEVIN
PHONE NUMBER	776103365
PLACE OF BIRTH	PARIS, FRANCE
ID	55555X
FAVORITE COLOUR	BLACK
FAVORITE MUSIC	ROCK
ID	55555X
UNIVERSITY	HARVARD
ID	666666X
NAME	JOHN
LAST NAME	TRICH
NATIONALITY	FRENCH
ID	666666X
ADDRESS	10 RUE STEVIN
PHONE NUMBER	776103365
PLACE OF BIRTH	PARIS, FRANCE
ID	666666X
FAVORITE COLOUR	PINK
FAVORITE MUSIC	ROCK
ID	666666X
UNIVERSITY	OXFORD

and I would like the following output:

ID	NAME	LAST NAME	NATIONALITY	ADDRESS	PHONE NUMBER	PLACE OF BIRTH	FAVORITE COLOUR	FAVORITE MUSIC	UNIVERSITY
55555X	JOHN	SMITH	FRENCH	10 RUE STEVIN	776103365	PARIS, FRANCE	BLACK	ROCK	HARVARD
666666X	JOHN	TRICH	FRENCH	10 RUE STEVIN	776103365	PARIS, FRANCE	PINK	ROCK	OXFORD

I have tried to use the multi-row formula but it hasn't work for me. At least partially since it created a new record per ID. 

I am sorry for the simplistic example, I am not allow to share actual examples. Any help will be appreciated. 

Regards, 

Giuliano

[grossal · Accepted solution]

Hi @giulianosua1,

here is a way to do it:

Output:

What happens:

Multi-Row-Formula:

This fills all the columns with the right IDs.

Filter:

Let's filter all the ID rows because they don't contain data.

Cross Tab:

Time to bring the data into shape!

Workflow attached. Let me know if you were looking for this.

Best

Alex

[giulianosua1 · Accepted solution]

Thank you very much!! it worked!

[grossal · Accepted solution]

I am glad it helped! 

Would you mind accepting it as a solution?

[giulianosua1 · Accepted solution]

Done! thank you Again!