topic Re: Split string in QlikView

Split string

Fri, 25 Nov 2011 22:24:37 GMT

Hi!

I want to split a string on ',' but only if it's not inside paranteses.

Ex. aa bb (c,c) dd,dd ss aa,ee rr ff dd should be three:

aa bb (c,c) dd

dd ss aa

ee rr ff dd

regards

thomas

Split string

Sat, 26 Nov 2011 09:25:09 GMT

you can try this function

TextBetween( s , beforetext , aftertext [, n ] )

Returns the text between the n:th occurrence of beforetext and the immediately following occurrence of aftertext within the string s.

Examples:

TextBetween('<abc>', '<', '>')	returns 'abc'
TextBetween('<abc><de>', '<', '>',2)

Re: Split string

swuehl — Sat, 26 Nov 2011 12:28:15 GMT

Thomas,

maybe like this in the script:

TEST:

LOAD subfield(Test,',') as TestSubfield INLINE [

Test

"aa bb (c,c) dd,dd ss aa,ee rr ff dd"

];

tmp:

LOAD *

,if(index(TestSubfield,')') and previous(index(TestSubfield,'(')), peek(TestSubfield)&','&TestSubfield,

if(findoneof(TestSubfield,'()')=0, TestSubfield, NULL())) as Result

resident TEST;

Result:

LOAD Result resident tmp where not IsNull(Result);

drop tables tmp, TEST;

Regards,

Stefan

Re: Split string

Mon, 28 Nov 2011 08:56:24 GMT

Stefan, this is what I were beginning with. First split and then "repair".Sorry to say that it don't work for my dataset, but I will try to refine it. Not blaming you since you only got a mockup dataset. Attaching an excel file with some data and desired outcome. Ignore Home- and Awayteam

Re: Split string

RSvebeck — Mon, 28 Nov 2011 09:40:07 GMT

Hi Thomas. I have a solution that might work. See attached. Regards Robert

Re: Split string

Mon, 28 Nov 2011 10:13:40 GMT

This does the trick:

Directory;
TEST:
LOAD
SubField(A,',') as TestSubfield
FROM
stringTest.xlsx
(ooxml, no labels, table is Blad1);
tmp:
LOAD *,
if(previous(index(TestSubfield,'(',2))=0,TestSubfield,
 if(index(TestSubfield,'(',2)>0 and previous(index(TestSubfield,'(',2))>0,TestSubfield,
 if(index(TestSubfield,'(',2)=0 ,peek(TestSubfield)&','&TestSubfield,NULL()))) as Result
resident TEST;
Result:
LOAD Result
 resident tmp 
where not IsNull(Result) AND
SubStringCount(Result,'(')=SubStringCount(Result,')');
DROP tables tmp,TEST;

I'll look at your solution as well Robert since mine probably is "ugly".