Help in Transpose

Question

Hi All, I am working with google API and have got the below output(Google API output from Alteryx). I want to transpose that data into the below format. Could someone please help me with the same? the JSON name output from google is dynamic. If there is a suite number attached to it, then the value shifts. Any help will be appreciated. As an alternative, can i use the data from column "result.adr_address" with REGEX to split the address into separate fields? Expected Output from below google API: street numberroutelocalityadministrative_area_level_2administrative_area_level_1countrypostal_coderesult.geometry.location.latresult.geometry.location.lng311Burnt Ridge RdBlackfaldsRed Deer CountyABCAT0M 0J052.2935953-113.8694788 Google API output from Alteryx JSON_NameJSON_ValueStringresult.address_components.0.long_name12345result.address_components.0.short_name12345result.address_components.0.types.0street_numberresult.address_components.1.long_nameBurnt Ridge Roadresult.address_components.1.short_nameBurnt Ridge Rdresult.address_components.1.types.0routeresult.address_components.2.long_nameBlackfaldsresult.address_components.2.short_nameBlackfaldsresult.address_components.2.types.0localityresult.address_components.2.types.1politicalresult.address_components.3.long_nameRed Deer Countyresult.address_components.3.short_nameRed Deer Countyresult.address_components.3.types.0administrative_area_level_2result.address_components.3.types.1politicalresult.address_components.4.long_nameAlbertaresult.address_components.4.short_nameABresult.address_components.4.types.0administrative_area_level_1result.address_components.4.types.1politicalresult.address_components.5.long_nameCanadaresult.address_components.5.short_nameCAresult.address_components.5.types.0countryresult.address_components.5.types.1politicalresult.address_components.6.long_nameT1M 0J0result.address_components.6.short_nameT1M 0J0result.address_components.6.types.0postal_coderesult.adr_address12345 Burnt Ridge Rd, Blackfalds, AB T1M 0J0, Canadaresult.formatted_address12345 Burnt Ridge Rd, Blackfalds, AB T1M 0J0, Canadaresult.geometry.location.lat11111result.geometry.location.lng22222

Chantelb · Answer

Hi @tahertalib ,

Yes you need a number for each address and then you can use this to group by on the cross-tab tools. If there is no number from the API you can create one using a multi-row formula. See updated workflow attached.

Cheers

JSON Parse Address.yxmd

tahertalib · Answer

Thanks @Chantelb

The solution works perfect. Had another query. If i try to replicate this for multiple addresses, it doesnt work as expected. Would you know how to deal with this if there are 100's of addresses?

Thanks

Chantelb · Answer

Hi @tahertalib ,

You can make it dynamic by using types.0 to name the columns. If there is a suite number it will have a new column which you can deselect if you wish. See workflow attached.

Let me know if this helps.

JSON Parse Address.yxmd