Transform Data

[Judicael]

Hello,

I want to transform the data :

Field Title:

{"DATASET":"1"}
{"SOFTWARE":"5"}
{"SOFTWARE":"5,2","DATASET":"2"}
{"VENDOR":"1"}
{"VENDOR":"1,2","JOB_TITLE":"1"}
{"VENDOR":"2"}
NULL

Into 

Title, Value

DATASET, 1
SOFTWARE, 5
SOFTWARE,5

SOFTWARE,2

DATASET, 2
VENDOR,1
VENDOR,1

VENDOR,2

JOB_TITLE,1

NULL

{"VENDOR":"2"}
NULL

Thanks for your help,

Regards.

[crusader_ · Accepted solution]

Hi,

First of all you would need to replace some symbols in your values to be able to distinguish "internal" and "external" separators and after that iteratively apply subfield() function to achieve desired result.

PFA example, however perhaps you would need to adjust it to your data.

Hope this helps

//Andrei

[qv_testing]

Hi, 

Try this...

Load *, PurgeChar(Subfield(Title, ':', 1), '"') as Feild;
Load *, Subfield(PurgeChar(Subfield(Title, ':', 2), '"'), ',') as Value;
Load * inline [
Title
"DATASET":"1"
"SOFTWARE":"5"
"SOFTWARE":"5,2","DATASET":"2"
"VENDOR":"1"
"VENDOR":"1,2","JOB_TITLE":"1"
"VENDOR":"2"
];

-Raju