Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
Michael_Tarallo
Employee
Employee

See how powerful FROM_FIELD() is. It helped me extract a simple JSON data structure from a field into it's own dimension table.

This is a simple example that works with flat json, meaning non-nested json hierarchies. However, you can use a combination of other functions like JSONGET() and JSONSET() to extract needed data. More examples on this to follow.

Playlist: https://www.youtube.com/playlist?list=PLW1uf5CQ_gSqF5bcmbBrk1q7Q4-h899V1

Qlik Help:

LOAD() https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularS...

JSONGET() https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunction...

 

 

Tags (1)
12 Comments
tan123qlik
Partner - Contributor III
Partner - Contributor III

Very good information.

Why is not JSON mention in the Qlik Help as a valid Format specification items? @Michael_Tarallo 

1,883 Views
CasperQlik
Creator
Creator

I get the following error when I use the From_Field function:

CasperQlik_0-1695719972461.png

 

This is my code:

 

CasperQlik_1-1695720069255.png

Any help is appreciated!

 

1,656 Views
Michael_Tarallo
Employee
Employee

Hi Capser - is your JSON complex or a flat single level structure?

This is a simple example that works with flat json, meaning non-nested json hierarchies.

If more complex - there are other ways and I have some examples I can share.

1,607 Views
AlexOmetis
Partner Ambassador
Partner Ambassador

As an aside @ChristofSchwarz  has a great article on a way to load data from JSON files (flat or nested) that I've been using recently... apparently there's a part 2 coming soon... 

1,585 Views
CasperQlik
Creator
Creator

I don't know how it qualifies but it is of the following format:

{
"lookupId": 123,
"lookupValue": "value",
"isSecretFieldValue": false
}

I would say it appears quite flat and not nested.

1,570 Views
Michael_Tarallo
Employee
Employee
  1. Hi Casper try this code snip in a new app - it worked fine for me.

You may have something else that is stopping the parser.

[data]:
load [id],[jsonfield] Inline [
id,jsonfield
1,'{"lookupId": 123,"lookupValue": "value","isSecretFieldValue": false}'
];

table1:
load *
from_field(data,jsonfield)(json,utf8, no labels);

Michael_Tarallo_0-1695831855068.png

1,542 Views
CasperQlik
Creator
Creator

Thank you for the assistance. I did some tinkering and it works with the code you provided.

Initially I got the same error as earlier: Cannot open file '***' , but I got the idea of removing any rows with no data. Several rows had no values in the JSON field. Therefore I only loaded rows with valid JSON data. This did the trick and I get the parsed data as expected.

1,509 Views
Michael_Tarallo
Employee
Employee

Nice! Thanks for letting us know 🙂

1,460 Views
scottduthie
Partner Ambassador
Partner Ambassador

Thanks @Michael_Tarallo .

I know your post was originally focussed on using the From_Field() function - but you mention methods to load in JSON flat files - and we've been seeing this use case a lot lately. How do you get a JSON file to upload into a Qlik SaaS tenant DataFiles folder within a space? If I try that I get the 'this file type is not supported' error message. Is there a trick to get around this - like changing the file ext for example? Or would you need to store the JSON files outside of Qlik Cloud?

1,413 Views
AlexOmetis
Partner Ambassador
Partner Ambassador

@scottduthie I just rename them to add .txt before uploading... or, as you say store them externally. 

1,384 Views