topic Transform Data in QlikView

Transform Data

Judicael — Sat, 16 Nov 2024 04:08:54 GMT

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.

Re: Transform Data

crusader_ — Mon, 18 Mar 2019 01:01:45 GMT

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

Re: Transform Data

Nagaraju_KCS — Mon, 18 Mar 2019 09:27:35 GMT

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