FROM_FIELD() - Is very powerful! It helped me pars... - Qlik Community

Michael_Tarallo · ‎2023-05-25

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

tan123qlik · ‎2023-06-22

Very good information.

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

CasperQlik · ‎2023-09-26

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

This is my code:

Any help is appreciated!

Michael_Tarallo · ‎2023-09-27

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.

AlexOmetis · ‎2023-09-27

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...

CasperQlik · ‎2023-09-27

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.

Michael_Tarallo · ‎2023-09-27

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

CasperQlik · ‎2023-09-28

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.

Michael_Tarallo · ‎2023-09-29

Nice! Thanks for letting us know 🙂

scottduthie · ‎2023-10-02

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?

AlexOmetis · ‎2023-10-03

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

FROM_FIELD() - Is very powerful! It helped me parse JSON data stored in a field!