Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have a delimiter seperated value in one column called `Tags`,
I have used Subfield function to seperate each values , but i am not able to get the value before the delimiter to fix it as column header,
example value of tag field:
{"application":"Domain Controllers","Environment":"Production","Owner":"Systems","CostCenter":"Infra","ApplicationService":"Active Directory","tagVM":"vmwcushnetdc02","tagOrigin":"Migrated"}
Expected output in image:
thanks in advance,
Regards,
Adithya
Looks like you're basically reading .json? Even if it's not a .json file, you should be able to adapt the same solution, I think.
https://community.qlik.com/t5/Member-Articles/How-to-read-json-files-with-Qlik-Sense/ta-p/2120598
Hi Or,
I am loading this from a database, hence i think we cant solve this using the method you have suggested.
I don't see why not, but perhaps I'm missing something?
This is the script,
test:
LOAD
Tags,
SubField(Tags,',',1) as A,
SubField(Tags,',',2) as B,
SubField(Tags,',',3) as C,
SubField(Tags,',',4) as D,
SubField(Tags,',',5) as E,
SubField(Tags,',',6) as F,
SubField(Tags,',',7) as G,
SubField(Tags,',',8) as H
FROM [lib://SharedDataSources:DataFiles/test.qvd] (qvd);
and the output i got is
but the expected output is attached in the question,
i am not able to use your method since its not a .json file
It doesn't need to be a file. You just need to use (json) to treat it as a Jason file. I used an inline load for mine, though it doesn't seem to be a documented option, with no issues:
Load * INLINE [
{"application":"Domain Controllers","Environment":"Production","Owner":"Systems","CostCenter":"Infra","ApplicationService":"Active Directory","tagVM":"vmwcushnetdc02","tagOrigin":"Migrated"}] (json);
I imagine if you read your original and then stored it into a text file or whatnot, you could read that file as json.
If that doesn't work for you, another approach you could use is:
https://community.qlik.com/t5/New-to-Qlik-Analytics/Qlik-Sense-parsing-of-a-json-field/td-p/1795740
I saved my table in Txt format and tried to read it as .json, but i am getting error.
write like this
subfield(tagfield,':',-1) as your_required
you will get a answer
@Dataguy46 Try below
Generic
Load Tag,
SubField(Values,':',1) as Field_Name,
SubField(Values,':',2) as Field_Values;
Load Tag,
SubField(PurgeChar(Tag,'"{}'),',') as Values
FROM [lib://SharedDataSources:DataFiles/test.qvd] (qvd);
Hello Venkat, tried that, but i need both attribute and values, like the screenshot below
and this my input example:
{"application":"Domain Controllers","Environment":"Production","Owner":"Systems","CostCenter":"Infra","ApplicationService":"Active Directory","tagVM":"vmwcushnetdc02","tagOrigin":"Migrated"}
and this is how the field tags looks like,