JSON Dynamically Mapping

[Masond3]

HI All, 

Need some help 

I am getting the following JSON Response. 

JSON_Name	JSON_ValueString
data.findCompanyByCompanyId.companyId	CDM001
data.findCompanyByCompanyId.companyIdentifiers.0.idType	FCA
data.findCompanyByCompanyId.companyIdentifiers.0.idValue	295268
data.findCompanyByCompanyId.companyIdentifiers.1.idType	CAS
data.findCompanyByCompanyId.companyIdentifiers.1.idValue	78047108
data.findCompanyByCompanyId.companyId	CDM002
data.findCompanyByCompanyId.companyIdentifiers.0.idType	LEI
data.findCompanyByCompanyId.companyIdentifiers.0.idValue	5493002O03IRC3O7AT88
data.findCompanyByCompanyId.companyId	CDM003
data.findCompanyByCompanyId.companyIdentifiers.0.idType	NAICS
data.findCompanyByCompanyId.companyIdentifiers.0.idValue	99999
data.findCompanyByCompanyId.companyId	CDM004
data.findCompanyByCompanyId.companyIdentifiers.0.idType	FCA
data.findCompanyByCompanyId.companyIdentifiers.0.idValue	079679

AIM - For every Company ID there might be referential Ids associated.

Every companyIdentifiers.0.idType represents the Name of the referential ID (ie, Naics, Lei, FCA, CasID), the IDvalue represents the value of the Referential ID

NB : At the moment idType are the only 4 values, however there are plans to bring in two new values in the next coming months. 

What i would like help with is, is there a way to dynamically identify the name in the idType and create a column Dynamically, and then put the corresponding idValue in the IDType against the corresponding companyId

Expected outcome 

companyId	FCA	CAS	LEI	NAICS
CDM001	295268	78047108
CDM002			5493002O03IRC3O7AT88
CDM003				99999
CDM004	079679

Looking forward to your response 

Many thanks 

Masond3

[binuacs]

@Masond3 One way of doing this

[Masond3]

@binuacs  Thank you for your swift response

I have updated the post to include the header names of "Json_Name" and "JSON_ValueString" This represents F1 & F2 as your headers in the input file. 

I have noticed whilst exploring the additional "JSON_Name" (F1) i have a row called "data.findCompanyByCompanyId.addresses.0.addressId" which also houses a CDM ID . (Example Value CDMA111

Looking at your Multi row formula "IIF(STartsWith([F2],'CDM'),[F2],[Row-1:CMD])" i can see that we are creating a new column called "CMD" and we assigning a new ID, anytime you find the Word "CDM" in columnd f2. 

Given that we potentially have an address id which also has CDM ID in the columns F2, is there any possibility to reference column f1 (Json Name) and anytime it mentions  "data.findCompanyByCompanyId.companyId" assign a new CMDID ? 

[binuacs]

@Masond3 Can you provide the input file with the above said data and expected output?

[Masond3]

@binuacs 
Here is an example of the input as you can see data.findCompanyByCompanyId.companyId and data.findCompanyByCompanyId.addresses.1.addressId both contain "CDM" Within the JSON_ValueString. That's why i was thinking everytime Json_Name says "data.findCompanyByCompanyId.companyId then we can assign a new CMD

i have attached  my attempt at the flow based on your initial flow  

JSON_Name	JSON_ValueString
data.findCompanyByCompanyId.companyId	CDM001
data.findCompanyByCompanyId.addresses.1.addressId	CDMA1111
data.findCompanyByCompanyId.addresses.1.street	2 RIVERSIDE BANCHORY KINCARDINESHIRE
data.findCompanyByCompanyId.addresses.1.city	UNITED KINGDOM
data.findCompanyByCompanyId.addresses.1.state
data.findCompanyByCompanyId.addresses.1.zipcode	AB316PS
data.findCompanyByCompanyId.addresses.1.country	GB
data.findCompanyByCompanyId.addresses.1.countryCd	GB
data.findCompanyByCompanyId.addresses.1.region	EMEA
data.findCompanyByCompanyId.companyIdentifiers.0.idType	FCA
data.findCompanyByCompanyId.companyIdentifiers.0.idValue	295268
data.findCompanyByCompanyId.companyIdentifiers.1.idType	CAS
data.findCompanyByCompanyId.companyIdentifiers.1.idValue	78047108
data.findCompanyByCompanyId.companyId	CDM002
data.findCompanyByCompanyId.addresses.1.addressId	CDMA1112
data.findCompanyByCompanyId.addresses.1.street	60 Victoria Embankment
data.findCompanyByCompanyId.addresses.1.city	London
data.findCompanyByCompanyId.addresses.1.state
data.findCompanyByCompanyId.addresses.1.zipcode	ec1 1nz
data.findCompanyByCompanyId.addresses.1.country	GB
data.findCompanyByCompanyId.addresses.1.countryCd	GB
data.findCompanyByCompanyId.addresses.1.region	EMEA
data.findCompanyByCompanyId.companyIdentifiers.0.idType	LEI
data.findCompanyByCompanyId.companyIdentifiers.0.idValue	5493002O03IRC3O7AT88
data.findCompanyByCompanyId.companyId	CDM003
data.findCompanyByCompanyId.addresses.1.addressId	CDMA1113
data.findCompanyByCompanyId.addresses.1.street	999 Nike Avenue
data.findCompanyByCompanyId.addresses.1.city
data.findCompanyByCompanyId.addresses.1.state
data.findCompanyByCompanyId.addresses.1.zipcode
data.findCompanyByCompanyId.addresses.1.country	GB
data.findCompanyByCompanyId.addresses.1.countryCd	GB
data.findCompanyByCompanyId.addresses.1.region	EMEA
data.findCompanyByCompanyId.companyIdentifiers.0.idType	NAICS
data.findCompanyByCompanyId.companyIdentifiers.0.idValue	99999
data.findCompanyByCompanyId.companyId	CDM004
data.findCompanyByCompanyId.addresses.1.addressId	CDMA1114
data.findCompanyByCompanyId.addresses.1.street	123 High Street
data.findCompanyByCompanyId.addresses.1.city
data.findCompanyByCompanyId.addresses.1.state	West Midlanda
data.findCompanyByCompanyId.addresses.1.zipcode	CV15NR
data.findCompanyByCompanyId.addresses.1.country	GB
data.findCompanyByCompanyId.addresses.1.countryCd	GB
data.findCompanyByCompanyId.addresses.1.region	EMEA
data.findCompanyByCompanyId.companyIdentifiers.0.idType	FCA
data.findCompanyByCompanyId.companyIdentifiers.0.idValue	79679

Expected out come 

companyId

FCA

CAS

LEI

NAICS

addressId

street

city

state

zipcode

country

countryCd

region

CDM001

295268

78047108

CDMA1111

2 RIVERSIDE BANCHORY KINCARDINESHIRE

UNITED KINGDOM

AB316PS

GB

GB

EMEA

CDM002

5493002O03IRC3O7AT88

CDMA1112

60 Victoria Embankment

London

ec1 1nz

GB

GB

EMEA

CDM003

99999

CDMA1113

999 Nike Avenue

GB

GB

EMEA

CDM004

79679

CDMA1114

123 High Street

West Midlanda

CV15NR

GB

GB

EMEA

[binuacs]

@Masond3 Updated workflow attached