Spliting Data

[neerajthakur]

Hi All,

I have a field having data in this form "field1":[null,"111","222","qwerty"],"field2":107,"field3":"NA","field4":[null,"11"]

How can I split this data using subfield and purgechar,

Expected result : 

Row 1 : field1:null,111,222,qwerty

Row 2: field2:107

Row 3: field3:NA

Row 4: null,11

[brunobertels]

hi try keepcher rather than purgechr :

keepchar(YourField,',:0123456789abcdefghijklmnopqrstuvwxyz' )

[neerajthakur]

It will work only to an extent, now how will I split the data into different rows ? as using subfield(A,',') will split this newly created value too.

Table:

Load A from Table1;

This field A contains data in above format. I want it to split it now into different rows like field1,field2,field3,field4

[MayilVahanan]

Hi

Might be, try like below

Load * Where A1 <> 'field';
LOAD A, 'field'&PurgeChar(SubField(Replace(A, '"',''), 'field'),'][') as A1
FROM
[209779.txt]
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

[neerajthakur]

Hi @MayilVahanan 

Field name can be anything I used field1 , .... as example here cannot use it a delimiter

[vinieme12 · Accepted solution]

try below

temp:
Load
if(index(Row,':'),SubField(Row,':',1),Peek('FieldLabel')) as FieldLabel
,PurgeChar(if(index(Row,':'),SubField(Row,':',2),Row),'[]') as Value
;
Load
subfield(@1,',') as Row
;
LOAD
@1
FROM [lib://AttachedFiles/blank.txt]
(txt, codepage is 28591, no labels, delimiter is '\t', msq);

Load FieldLabel,Concat(trim(Value),',') as RowValues
Resident temp
Group by FieldLabel;
Drop table temp;

exit Script;