Remove duplicate values in JSON file

Please include your data instead of screenshots. You'd first want to look at using a unique tool to get rid of the duplicates. Otherwise the JSON Build tool should do the trick.

spuri5

Please find the sample data below:

Record ID	Name	Value
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2027-04-05
1	Location.lpCode	AP0BUY
1	Location.lpDescription	AA
1	Location.lpYear	2090
1	zipCode	FFAQ,FI19IA
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2029-04-06
1	Location.lpCode	AP0ABC
1	Location.lpDescription	ZZ
1	Location.lpYear	2078
1	zipCode	FFAQ,FI19IA
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2033-04-06
1	Location.lpCode	AP1LKJ
1	Location.lpDescription	QQ
1	Location.lpYear	2087
1	zipCode	FFAQ,FI19IA
2	AreaCode	CD123
2	AreaName	CD123_name
2	StreetCode	ABT
2	Location.Date	4/6/2032
2	Location.lpCode	AP1GTB
2	Location.lpDescription	BH
2	Location.lpYear	2094
2	zipCode	FFAQ,FI19IA,SGHTWY
2	AreaCode	CD123
2	AreaName	CD123_name
2	StreetCode	ABT
2	Location.Date	4/6/2030
2	Location.lpCode	AP1QWR
2	Location.lpDescription	OI
2	Location.lpYear	2077
2	zipCode	FFAQ,FI19IA,SGHTWY

Here's how you can do this:

Record ID to keep order
Unique tool to remove dupes
Sort to put back in order (based on record ID)
JSON Build
Summarize to create the list

New Workflow3.yxmd

spuri5

Hi Luke,

Below is the output I am getting with your workflow:

[
{
"AreaCode":"AB123",
"AreaName":"AB123_name",
"StreetCode":"TRC",
"Location":{
"Date":"2027-04-05",
"lpCode":"AP0BUY",
"lpDescription":"AA",
"lpYear":"2090"
},
"zipCode":"FFAQ,FI19IA",
"Location":{
"Date":"2029-04-06",
"lpCode":"AP0ABC",
"lpDescription":"ZZ",
"lpYear":"2078",
"Date":"2033-04-06",
"lpCode":"AP1LKJ",
"lpDescription":"QQ",
"lpYear":"2087"
}
},
{
"AreaCode":"CD123",
"AreaName":"CD123_name",
"StreetCode":"ABT",
"Location":{
"Date":"4/6/2032",
"lpCode":"AP1GTB",
"lpDescription":"BH",
"lpYear":"2094"
},
"zipCode":"FFAQ,FI19IA,SGHTWY",
"Location":{
"Date":"4/6/2030",
"lpCode":"AP1QWR",
"lpDescription":"OI",
"lpYear":"2077"
}
}
]

But the expected out should be as shown below: Where the location should be appearing once!

[
{
"AreaCode":"AB123",
"AreaName":"AB123_name",
"StreetCode":"TRC",
"zipCode":"FFAQ,FI19IA",
"Location":[
{
"Date":"2027-04-05",
"lpCode":"AP0BUY",
"lpDescription":"AA",
"lpYear":"2090"
},
{
"Date":"2029-04-06",
"lpCode":"AP0ABC",
"lpDescription":"ZZ",
"lpYear":"2078"
},
{
"Date":"2033-04-06",
"lpCode":"AP1LKJ",
"lpDescription":"QQ",
"lpYear":"2087"
}
]
},
{
"AreaCode":"CD123",
"AreaName":"CD123_name",
"StreetCode":"ABT",
"zipCode":"FFAQ,FI19IA,SGHTWY",
"Location":[
{
"Date":"2032-04-06",
"lpCode":"AP1GTB",
"lpDescription":"BH",
"lpYear":"2094"
},
{
"Date":"2030-04-06",
"lpCode":"AP1QWR",
"lpDescription":"OI",
"lpYear":"2077"
}
]
}
]

I see, let me look.

Give this a go. Had to handle the multiple locations per record.

For future reference, the best way to figure out how to structure your data is to have your expected JSON as an input and see what the JSON Parse tool gives you. Then you just need to get alteryx to put the data in that format ahead of a JSON Build to get the same results

New Workflow3.yxmd

spuri5

Hi Luke,

Thank you so much for your help, it was my bad I forgot to add the values of the attributes (Location.Date and Location.lpCode) within the "location" container that can be repeated for a larger set of data. Hence uniquing them out based on record id, name, and value is eliminating the duplicates which shouldn't be the case.

We want to retain all values as shown below.

Input File: Please use this file as an Input

Record ID	Name	Value
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2027-04-05
1	Location.lpCode	AP0BUY
1	Location.lpDescription	AA
1	Location.lpYear	2090
1	zipCode	FFAQ,FI19IA
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2027-04-05
1	Location.lpCode	AP0BUY
1	Location.lpDescription	ZZ
1	Location.lpYear	2078
1	zipCode	FFAQ,FI19IA
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2033-04-06
1	Location.lpCode	AP1LKJ
1	Location.lpDescription	QQ
1	Location.lpYear	2087
1	zipCode	FFAQ,FI19IA
2	AreaCode	CD123
2	AreaName	CD123_name
2	StreetCode	ABT
2	Location.Date	4/6/2032
2	Location.lpCode	AP1GTB
2	Location.lpDescription	BH
2	Location.lpYear	2094
2	zipCode	FFAQ,FI19IA,SGHTWY
2	AreaCode	CD123
2	AreaName	CD123_name
2	StreetCode	ABT
2	Location.Date	2030-04-06
2	Location.lpCode	AP1QWR
2	Location.lpDescription	OI
2	Location.lpYear	2094
2	zipCode	FFAQ,FI19IA,SGHTWY

Just a matter of only checking non-location fields for duplicates.

New Workflow3.yxmd

thank you so much Luke, this workflow was really helpful for me as well.

Luke,

Building on the same data, what if I have multiple containers similar to location, then what do I do? I have attached sample data and pasted my expected outcome as its in json format. Thanks for helping me out.

[
{
"AreaCode":"AB123",
"AreaName":"AB123_name",
"StreetCode":"TRC",
"zipCode":"FFAQ,FI19IA",
"Location":[
{
"Date":"2027-04-05",
"lpCode":"AP0BUY",
"lpDescription":"AA",
"lpYear":"2090"
},
{
"Date":"2029-04-06",
"lpCode":"AP0ABC",
"lpDescription":"ZZ",
"lpYear":"2078"
},
{
"Date":"2033-04-06",
"lpCode":"AP1LKJ",
"lpDescription":"QQ",
"lpYear":"2087"
}
]
"Fruit":[
{
"Fruit.Apple":"ABC",
"Fruit.No":"12",
},
{
"Fruit.Apple":"DEF",
"Fruit.No":"12",
}
]
},
{
"AreaCode":"CD123",
"AreaName":"CD123_name",
"StreetCode":"ABT",
"zipCode":"FFAQ,FI19IA,SGHTWY",
"Location":[
{
"Date":"2032-04-06",
"lpCode":"AP1GTB",
"lpDescription":"BH",
"lpYear":"2094"
},
{
"Date":"2030-04-06",
"lpCode":"AP1QWR",
"lpDescription":"OI",
"lpYear":"2077"
}
]
"Fruit":[
{
"Fruit.Apple":"ABC",
"Fruit.No":"12",
},
{
"Fruit.Apple":"DEF",
"Fruit.No":"23",
}
]
}
]

SampleInput.xlsx

Hi @jakaur32

You would just need to update the workflow to have an OR condition in the filter tool and update the multi-row tool to also have an OR to check for the first field of Fruit. Give that a go and attach a workflow if you have any issues. If you have more than 2 nested fields there would probably be better ways to dynamically approach this.

As always, the best way to figure out how to create JSON with alteryx is to use the JSON Parse tool on your expected JSON, see how it comes out, and use Alteryx to put your data into that exact format.

Luke,

So I tried to do what you suggested but I am getting negative numbers in my output. and attaching the workflow for reference, thank you.

New Workflow3 (1)(1).yxmd

Hi @jakaur32

Your multi-row formula isn't valid. Try:

IF [Name] IN( 'Location.Date', 'Fruit.Apple')
then [Row-1:Grouping]+1
else [Row-1:Grouping]
endif

Also make sure you increase the field length for 'Name'. You can do it in the select tool.

d22e3bbe-9347-4e27-b821-860cee6ba5ca.PNG

Luke,

The expected outcome that i am supposed to be getting is not matching with the final outcome. I have attached a screenshot of the outcome after making the changes as suggested (please see d22e3bbe-.PNG) . My actual come should look like the 2nd attachment (88bafefe.PNG), please advise.

88bafefe-9bb9-4cda-beec-4b982253fa76.PNG