Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Zurich on Sept 24th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
lalitkgehlot89
Partner - Creator II
Partner - Creator II

Json Data Cleaning

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

 

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@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;

 

Screenshot 2024-10-16 at 12.42.42.png

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);

 

 

View solution in original post

3 Replies
Kushal_Chawda

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

lalitkgehlot89
Partner - Creator II
Partner - Creator II
Author

Yes branch_id is unique.

 

Kushal_Chawda

@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;

 

Screenshot 2024-10-16 at 12.42.42.png

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);