topic Delimited seperated values as column header in App Development

Delimited seperated values as column header

Dataguy46 — Tue, 06 Aug 2024 12:34:09 GMT

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

Re: Delimited seperated values as column header

Or — Tue, 06 Aug 2024 13:07:45 GMT

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

Re: Delimited seperated values as column header

Dataguy46 — Tue, 06 Aug 2024 13:14:16 GMT

Hi Or,

I am loading this from a database, hence i think we cant solve this using the method you have suggested.

Re: Delimited seperated values as column header

Or — Tue, 06 Aug 2024 13:25:46 GMT

I don't see why not, but perhaps I'm missing something?

Re: Delimited seperated values as column header

Dataguy46 — Tue, 06 Aug 2024 13:32:29 GMT

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

Re: Delimited seperated values as column header

Or — Tue, 06 Aug 2024 13:40:27 GMT

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

Re: Delimited seperated values as column header

Dataguy46 — Thu, 08 Aug 2024 12:18:17 GMT

I saved my table in Txt format and tried to read it as .json, but i am getting error.

Re: Delimited seperated values as column header

Venkat7 — Thu, 08 Aug 2024 12:45:31 GMT

write like this

subfield(tagfield,':',-1) as your_required

you will get a answer

Re: Delimited seperated values as column header

Kushal_Chawda — Thu, 08 Aug 2024 13:07:50 GMT

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

Re: Delimited seperated values as column header

Dataguy46 — Thu, 08 Aug 2024 13:09:45 GMT

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,

Re: Delimited seperated values as column header

Dataguy46 — Thu, 08 Aug 2024 13:14:00 GMT

I got this error

Re: Delimited seperated values as column header

Kushal_Chawda — Thu, 08 Aug 2024 13:17:00 GMT

@Dataguy46 are you loading data from QVD? I am assuming you have Tag field available in QVD. You need to write my script while loading data from QVD.

Re: Delimited seperated values as column header

Dataguy46 — Thu, 08 Aug 2024 13:18:50 GMT

Hi Kushal,

Yes, loading from a qvd file, see the below script and the column which needs parsing is `Tags`

testparsing:
Generic
Load Tags,
SubField(Values,':',1) as Field_Name,
SubField(Values,':',2) as Field_Values;
Load Tags,
SubField(PurgeChar(Tags,'"{}'),',') as Values
FROM [lib://SharedDataSources:DataFiles/AzureCostHistory.qvd] (qvd);

Re: Delimited seperated values as column header

Kushal_Chawda — Thu, 08 Aug 2024 13:20:13 GMT

@Dataguy46 try below

Generic
Load *
where len(trim(Field_Name))>0;
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);

Re: Delimited seperated values as column header

Dataguy46 — Thu, 08 Aug 2024 13:24:28 GMT

I got the output like this,

Re: Delimited seperated values as column header

Venkat7 — Thu, 08 Aug 2024 13:43:30 GMT

separate two fields and apply cross table

Re: Delimited seperated values as column header

Kushal_Chawda — Thu, 08 Aug 2024 14:03:35 GMT

@Venkat7 are you using entire script? first step is below

SubField(PurgeChar(Tag,'"{}'),',') as Values

You can share sample QVD if it doesn't work