Json Data Cleaning

lalitkgehlot89 — Fri, 15 Nov 2024 20:51:08 GMT

Hi,

I need help to clean the nested json data. Sample data file are attcahed.

Required output format is mentioned below.

BRANCH_ID

assessmentYear

salesGrossReceiptsOFBusiness

Re: Json Data Cleaning

Kushal_Chawda — Wed, 16 Oct 2024 10:50:27 GMT

@lalitkgehlot89 Is the branch_id unique for each attribute_key and attribute_value?

Re: Json Data Cleaning

lalitkgehlot89 — Wed, 16 Oct 2024 11:04:29 GMT

Yes branch_id is unique.

Re: Json Data Cleaning

Kushal_Chawda — Wed, 16 Oct 2024 11:46:22 GMT

@lalitkgehlot89 In your json data you don't have Branch_Id which means you may need to loop over all branch and get the json data and process it. Otherwise it would have been easy without loop, if branch_id was part of json data.

Data: LOAD ATTRIB_KEY, BRANCH_ID, ATTRIB_VALUE FROM [lib://Data/Sample Data.xlsx] (ooxml, embedded labels, table is Sheet1); Expanded_json: Load * Inline [ Junk ]; for Each vBranch in FieldValueList('BRANCH_ID') Branch: Load ATTRIB_VALUE as JsonText Resident Data where BRANCH_ID='$(vBranch)'; Concatenate(Expanded_json) Load *, '$(vBranch)' as BRANCH_ID From_Field (Branch,JsonText)(json,utf8,no labels); Drop Table Branch; Next vBranch Drop Field Junk;

if you don't want json data to be segregated by branch id then you can simply do below

Data:
LOAD
ATTRIB_KEY,
BRANCH_ID,
ATTRIB_VALUE
FROM [lib://SharedFolder_ChADS/Data/Files/Json/Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Expanded_json:
Load *
From_Field
(Data,ATTRIB_VALUE)(json,utf8,no labels);

topic Re: Json Data Cleaning in App Development

Json Data Cleaning

Re: Json Data Cleaning

Re: Json Data Cleaning

Re: Json Data Cleaning