Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Need help splitting a field using the first delimiter from the right.

I've been using the script below to split a field into two:

If(Index(AccountTemp,' ')=0,Null(),SubField(AccountTemp,' ',1))                      AS Account,

If(Index(AccountTemp,' ')=0,AccountTemp,SubField(AccountTemp,' ',2))          AS AccountGroup,

which works for:

Denver Admin ---> Denver,      Admin,

But doesn't work for:

Washington DC Admin ------->               Washington,                DC,               Admin,

Is there a way to split a field using the first space in the data from the right?

1 Solution

Accepted Solutions
bruno_montenegr
Contributor III

Re: Need help splitting a field using the first delimiter from the right.

Hello, Trey.

Try this:

Left(AccountTemp, Index(AccountTemp, ' ', -1) - 1)    as Account,

Mid(AccountTemp, Index(AccountTemp, ' ', -1) + 1)   as AccountGroup,

Index() with a -1 third parameter finds the first occurrence of the substring from the right.

Regards.

4 Replies
mov
Esteemed Contributor III

Re: Need help splitting a field using the first delimiter from the right.

From the right, use -1

bruno_montenegr
Contributor III

Re: Need help splitting a field using the first delimiter from the right.

Hello, Trey.

Try this:

Left(AccountTemp, Index(AccountTemp, ' ', -1) - 1)    as Account,

Mid(AccountTemp, Index(AccountTemp, ' ', -1) + 1)   as AccountGroup,

Index() with a -1 third parameter finds the first occurrence of the substring from the right.

Regards.

Not applicable

Re: Need help splitting a field using the first delimiter from the right.

Thanks Bruno, that worked!

Not applicable

Re: Need help splitting a field using the first delimiter from the right.

HI Trey,

Kindly Try Like this ..

Test:

LOAD * INLINE [

   AccountTemp

    Denver Admin

    Washington DC Admin

];

load

left( AccountTemp,index(AccountTemp,' ',-1))as Account,

Right(AccountTemp,len(AccountTemp)-index(AccountTemp,' ',-1))as AccountGroup

Resident Test;

Drop Table Test;

Thanks!!!

Community Browser