topic Re: splitting a field into seperate fields between 2 different delimiters in QlikView

splitting a field into seperate fields between 2 different delimiters

hopkinsc — Fri, 22 May 2015 11:44:35 GMT

Hi All,

I have a field (GCELL) that has values like...

LABEL=TEST-1, CellIndex=0, CGI=619054445D955

I want to split these into their own fields as follows..

NewField1

TEST-1

NewField2

NewField3

619054445D955

can anyone help please? I have tried the subfield function which works for NewField3 but i can't get the other 2 without duplicating rows of data.

See attached file..

Re: splitting a field into seperate fields between 2 different delimiters

swuehl — Fri, 22 May 2015 11:53:59 GMT

Maybe like

Data:

Load *,

subfield(Subfield(GCELL,',',1),'=',2) as NewField1,

subfield(Subfield(GCELL,',',2),'=',2) as NewField2,

subfield(Subfield(GCELL,',',3),'=',2) as NewField3

Resident TestData;

Re: splitting a field into seperate fields between 2 different delimiters

sunny_talwar — Fri, 22 May 2015 11:55:11 GMT

May be this:

TestData:

Load * inline [

GCELL

'LABEL=TEST-1, CellIndex=0, CGI=619054445D955'

];

Data:

Load *,

Subfield(Subfield(GCELL,'=',2),', ', 1) as NewField1,

SubField(SubField(GCELL, '=', 3),', ', 1) as NewField2,

Subfield(GCELL,'=',4) as NewField3

Resident TestData;

DROP table TestData;

Output:

Re: splitting a field into seperate fields between 2 different delimiters

anbu1984 — Fri, 22 May 2015 12:04:04 GMT

You can get result using TextBetween() just once like below

Load *,TextBetween(GCELL,'=',',',1) as NewField1,
TextBetween(GCELL,'=',',',2) as NewField2,
TextBetween(GCELL &',','=',',',3) as NewField3 inline [
GCELL
'LABEL=TEST-1, CellIndex=0, CGI=619054445D955'
];