Delimited seperated values as column header

[Dataguy46]

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

[Dataguy46]

I got this error

[Kushal_Chawda]

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

[Dataguy46]

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

[Kushal_Chawda]

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

[Dataguy46]

I got the output like this,

[Venkat7]

separate two fields and apply cross table 

[Kushal_Chawda]

@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