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

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

Fri, 20 Jun 2014 17:40:10 GMT

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?

Anonymous — Fri, 20 Jun 2014 17:45:49 GMT

From the right, use -1

Anonymous — Fri, 20 Jun 2014 17:53:08 GMT

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.

Fri, 20 Jun 2014 17:57:04 GMT

Thanks Bruno, that worked!

Fri, 20 Jun 2014 18:16:47 GMT

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!!!