JSON To Table format

[tjamal1]

I am trying to Parse JSON and then convert into the table format 

I am getting this result after parsing and some cleansing 

JSON_Name	JSON_ValueString
id	4780
employeename	Paris Hessel
employeesalary	1860
employeeage	56
profileimage
id	4782
employeename	Chere Funk I
employeesalary	1252
employeeage	67
profileimage

I want to convert it into Table  format like this

id	employeename	employeesalary	employeeage	profileimage
4780	Paris Hessel	1860	56
4782	Chere Funk I	1252	67

[Thableaus]

Hi @tjamal1 

You can use a combination of Multi-Row Formula with Transpose Tool.

- IF [JSON_Name] = "id" then [Row-1:Rank] + 1
ELSE [Row-1:Rank] ENDIF - Multi-Row Formula

- Transpose grouping by the Rank field created in the Multi-Row Formula Tool.

WF attached.

Cheers,

[JosephSerpis · Accepted solution]

Hi @tjamal1  I made a small change to your workflow and got the results you described.

[tjamal1]

Thanks for the reply 

Its working for me. 

An elegant use of Regex 🙂 

[tjamal1]

Thanks for the reply.

Its working.

Can you please briefly elaborate Multi row formula you have used 

[Thableaus]

@tjamal1 

It's pretty simple. You're basically grouping values in your column with a row number (rank) when you see the word "id".

IF [JSON_Name] = "id" then [Row-1:Rank] + 1
ELSE [Row-1:Rank] ENDIF

If you happen to find "id" in your JSON_Name, you add 1 row number (+1)

else you're in the same row as your "id", so just repeat the row before ([Row-1:Rank])

Cheers, 

[Kristaps]

Hi Joseph,

I am starter in Alteryx and tried to replicate your logic, however I am getting error "Couldn't resolve host name".  I am taking data directly from Salesforce Input. I am trying to get data from existing reports, however I am just getting one line "JSON".  My goal is to get this is table format.

Thank you very much in advance,

Kris

[jayviz]

This helped me to parse that data and convert to a table format.

Is it possible to define data types for the columns parsed from the json ?