Remove duplicate values in JSON file

[spuri5]

Hello, 

In my input file, I have Areacode, Areaname, StreetCode, and Zipcode, repeating multiple times and i just need it to show only once like in my expected outcome.

Also i want location to have nested values under location as shown below in the expected output.

Sample Input:

Expected Output:

[jakaur32]

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",
}
]
}
]

[Luke_C]

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. 

[jakaur32]

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.

[Luke_C]

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.

[jakaur32]

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.

[Luke_C]

Hi There - multiple fruit is a new requirement! There's probably a better way to make that dynamic using a tile tool or some sort of multi-row formula. I'd urge you to try on your own and report back if there's any issues. That's the best way to learn!

What does the JSON look like after you use the JSON Parse tool? That should give you a good starting point to work towards.