Converting JSON to Tabular

[Sarath27]

Input

dqm_solution_execution_id	run_date	data_entry_table	Failed_DQ_Checks_Count	Failed_DQ_Checks	DQ_Checks_InfoJSON
A	1/30/2022	AAB	5	MC_103,MC_502,MC_512,MC_514,MC_500	{"rule_id":"MC_103","rule_name":","Break_Fields":""},{"rule_id":"MC_502","rule_name":"","":"Additional_Name_1"},{"rule_id":"MC_512","rule_name":"","Break_Fields":""},{"rule_id":"MC_514","rule_name":"","Break_Fields":""},{"rule_id":"MC_500","rule_name":"","Break_Fields":""}

Required Output

dqm_solution_execution_id	run_date	data_entry_table	Failed_DQ_Checks_Count	Failed_DQ_Checks	rule_id	rule_name	breakfield
A	1/30/2022	AAB	5	MC_103,MC_502,MC_512,MC_514,MC_500	MC_103	Entries such as 'test', 'Dummy', 'NO USAR', 'DO NOT USE', '!DNC!', 'DNC', '*DNC*' are not allowed as a full value are part value in the String.	Party_Full_Name
A	1/31/2022	AAB	5	MC_103,MC_502,MC_512,MC_514,MC_501	MC_502	Entries such as 'test', 'Dummy', 'NO USAR', 'DO NOT USE', '!DNC!', 'DNC', '*DNC*' are not allowed as a full value are part value in the String.	Break_Fields
A	2/1/2022	AAB	5	MC_103,MC_502,MC_512,MC_514,MC_502	MC_512	Apply Free Text Language checks	Additional_Name_2
A	2/2/2022	AAB	5	MC_103,MC_502,MC_512,MC_514,MC_503	MC_514	Entries such as 'test', 'Dummy', 'NO USAR', 'DO NOT USE', '!DNC!', 'DNC', '*DNC*' are not allowed as a full value are part value in the String.	Additional_Name_2
A	2/3/2022	AAB	5	MC_103,MC_502,MC_512,MC_514,MC_504	MC_504	Apply Free Text Language checks	Additional_Name_1

Is there any way we could achieve this in Alteryx workflow? I tried already, but first array in DQ_Checks_InfoJSON is getting transformed.

[Luke_C]

Hi @Sarath27 

Can you attach the workflow you attempted?

[Sarath27]

Hi Luke,

Here it is

[Luke_C · Accepted solution]

Hi @Sarath27 

How's this? I didn't really change anything outside of updating the data to have a pipe to make the text-to-column easier (I saw you had a pipe in there already, so maybe you already did this?). I also tweaked the second JSON input as it looked like it was missing the 'Break_Field' text.

[Luke_C]

Hi @Sarath27 

There's no data - note the empty quotes highlighted below:

[Sarath27]

Hi Luke,

Thanks much. Your logic works here! Its working. If there is anything in this, I will ask here. Thanks again.

[Sarath27]

Hi Luke,

I have several data to be converted like this, but when I tried with this same logic I am getting rule_id concatendated in it. I also tried to find it with Unique tool, but still getting same issue. I included data with all the scenarios as an attachment, please try the same with your workflow.

Why I am getting like this below? I even tried with unique toll, but still same issue, please advice

solution_id is the relationship for all the rule_id, rule_name, break_fields.

[Luke_C · Accepted solution]

Hi @Sarath27 

Most of your data seems duplicated. Putting a unique tool on all fields gives ~100 rows. A few notes as to why you may have seen this issue:

1. The crosstab tool was set to concatenate data if multiple records existed for the groupings. Since there was a high volume of duplicate records, this happened.
2. The solution_id field name changed, so you may have gotten an error because of that.

Check out this workflow and let me know how it looks. Don't forget to tag the solution if you're all set.