โ12-14-2017 11:05 AM
I have CSV similar to:
Id,FName,Lname,street,city,state
15,Mary,Smith,123 Main,Anytown,NY
15,Mary,Smith,456 Elm,Sometown,PA
22,Joe,Know,123 4th,BigTown,NJ
22,Joe,Know,456 Shade,NoTown,VA
There can be any number of the idโs which means that any person can have any number of addresses. What I want to do is transform this to group on the idโs so the addresses are an array of objects under the name. This seems very similar to Transformation for data or Map data to json structure - #3 by eguo but just different enough where I am not able to figure it out.
Target JSON would be:
[
{
โattributesโ: {
โFirstNameโ: [
{
โvalueโ: โMaryโ
}
],
โLastNameโ: [
{
โvalueโ: โSmithโ
}
],
โAddressโ: [
{
โvalueโ: {
โAddressLine1โ: [
{
โvalueโ: โ123 Main Stโ
}
],
โCityโ: [
{
โvalueโ: โAnytownโ
}
],
โStateProvinceโ: [
{
โvalueโ: โNYโ
}
]
}
},
{
โvalueโ: {
โAddressLine1โ: [
{
โvalueโ: โ456 Elm Stโ
}
],
โCityโ: [
{
โvalueโ: โSometownโ
}
],
โStateProvinceโ: [
{
โvalueโ: โPAโ
}
]
}
}
]
}
},
{
โattributesโ: {
โFirstNameโ: [
{
โvalueโ: โJoeโ
}
],
โLastNameโ: [
{
โvalueโ: โKnowโ
}
],
โAddressโ: [
{
โvalueโ: {
โAddressLine1โ: [
{
โvalueโ: โ123 4thโ
}
],
โCityโ: [
{
โvalueโ: โBigtownโ
}
],
โStateProvinceโ: [
{
โvalueโ: โNJโ
}
]
}
},
{
โvalueโ: {
โAddressLine1โ: [
{
โvalueโ: โ456 Shadeโ
}
],
โCityโ: [
{
โvalueโ: โNoTownโ
}
],
โStateProvinceโ: [
{
โvalueโ: โVAโ
}
]
}
}
]
}
}
]
Solved! Go to Solution.
โ12-14-2017 02:25 PM
Attached is an option using the Group By Fields snap. It can likely be improved.
community-1806_2017_12_14.slp (6.3 KB)
โ12-14-2017 02:25 PM
Attached is an option using the Group By Fields snap. It can likely be improved.
community-1806_2017_12_14.slp (6.3 KB)
โ12-14-2017 03:55 PM
Wow! You are quite the mapping expert!
i saw the jsonPath.map method, but couldnโt really figure it out. would you indulge me with an explanation of the x=> part of the expression?
โ12-14-2017 05:09 PM
No. Iโm a better doer than explainer. Besides, youโre โฆTooOldToLearn anyway. ๐
An expert can follow up to correct me, but Iโll do my best to explainโฆ The โx=>โ is known in JavaScript as an arrow function. Itโs kind of shorthand for an anonymous function. Itโs similar to whatโs known as a lambda expression in Java and C#.
In this case, itโs used as a callback function to the Array.map() method. The map() method creates a new array with the results of the callback function as it iterates through each element in the array. So โxโ in โx=>โ represents the individual array element. The code after the โ=>โ is the logic to be performed with โxโ or other globally scoped variables. The results of the function are appended to the output array.
I hope that helps.
โ12-14-2017 03:30 PM
Here is another method, more Snaps, just a different approach.
PivotAddresses_2017_12_14.slp (8.2 KB)