Input dqm_solution_execution_idrun_datedata_entry_tableFailed_DQ_Checks_CountFailed_DQ_ChecksDQ_Checks_InfoJSONA1/30/2022AAB5MC_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_idrun_datedata_entry_tableFailed_DQ_Checks_CountFailed_DQ_Checksrule_idrule_namebreakfieldA1/30/2022AAB5MC_103,MC_502,MC_512,MC_514,MC_500MC_103Entries 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_NameA1/31/2022AAB5MC_103,MC_502,MC_512,MC_514,MC_501MC_502Entries 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_FieldsA2/1/2022AAB5MC_103,MC_502,MC_512,MC_514,MC_502MC_512Apply Free Text Language checksAdditional_Name_2A2/2/2022AAB5MC_103,MC_502,MC_512,MC_514,MC_503MC_514Entries 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_2A2/3/2022AAB5MC_103,MC_502,MC_512,MC_514,MC_504MC_504Apply Free Text Language checksAdditional_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.

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: * 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. * 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.

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.

Datasets

Data Investigation

Accepted answers

Luke_C

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.

Sample.yxmd

Luke_C

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:

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.
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.

Sample.yxmd

All comments

Luke_C

Hi @Sarath27

Can you attach the workflow you attempted?

Sarath27

Hi Luke,

Here it is

Hi @Sarath27

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.

Luke_C

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:

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.
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.

Sample.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703