Removing duplicate from json_parse output

Question

Hi All,

I am having output in json format where columns JSON_Names contains header of columns and JSON_ValueString contains values against the header.

Before transposing I am generating ID based on column CountryCode which is duplicate records in the extract and EntityId is also duplicate and there are no other column based on which unique ID can be generated.

Using this code - IF JSON_Names ='CountryCode' THEN [Row-1:Record ID]+1 ELSE [Row-1:Record ID] ENDIF  - in multi-row formula, wherever it is finding countrycode it is assigning new ID even if the country code contains the same value or repeated in JSON_ValueString column. That is why I want to remove the duplicae records before transposing and ID generation

How I can remove the duplicate entries before I generate the unique ID in this json extract. attached the extract sample.

Appreciate the help.

Regards,

AJuliet.

json_parse.xlsx

AJuliet · Answer

@grazitti_sapna , Yes it is but this is what I am doing before processing the data per your designed WF. CountryCode can be same for different EntityIDs so the join that we are applying based on RecordID will not work. Also the KPICode is repeated against the CountryCode(I took only one as example before) which I am able to delimit using text to column based on KPICode as comma separator.

grazitti_sapna · Answer

@AJuliet , I think this post is related to the same process which I resolved initially, the best workaround to remove duplicates would be to use unique on the basis of Country code after cross tab then you will get just 1 record corresponding to each country code