topic Re: trim all before last space? in QlikView

trim all before last space?

Mon, 22 Dec 2014 12:12:28 GMT

Hi,

Not a Qlikview developer, I've the formula working in excel but cant quite get it right in QV Not sure if QV has something similar to the "search" formula in excel?

=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,1)+1))

Thanks a lot in advance and happy christmas

Dan

Re: trim all before last space?

Gysbert_Wassenaar — Mon, 22 Dec 2014 12:16:00 GMT

Try subfield(B2,' ', -1)

And the equivalent of Excels Search() function is the Index() function.

Re: trim all before last space?

MarcoWedel — Mon, 22 Dec 2014 12:25:43 GMT

Mid(B2,Index(B2,' ',2)+1)

Re: trim all before last space?

Mon, 22 Dec 2014 13:21:15 GMT

This answer replicates exactly my excel formula, however I realised my formula in excel wasn't working correctly for some cases.

Gysbert's formula worked better for me.

Thanks both!!

Re: trim all before last space?

its_anandrjs — Mon, 22 Dec 2014 13:54:27 GMT

Can you post any example here.

Regards

Anand

Re: trim all before last space?

saurabh5 — Mon, 22 Dec 2014 14:10:00 GMT

hi Daniel,

if it is just trimming last few spaces then you can use rtrim() function, this will remove spaces from right side of the string.

Regards

Saurabh

Re: trim all before last space?

Mon, 22 Dec 2014 14:35:09 GMT

I have a field that contains information like this:

AB Leichire Heah POS0953

I only need the last part (POS0953) to create a new field with it.

The suggested by Gysbert works beautifuly:

subfield(B2,' ', -1)

Regards,

Dan

Re: trim all before last space?

MarcoWedel — Mon, 22 Dec 2014 14:47:36 GMT

please mark Gysbert's answer correct.

thanks

regards

Marco

Re: trim all before last space?

its_anandrjs — Mon, 22 Dec 2014 14:52:13 GMT

Yes Gysbert is right

for pick last right value from AB Leichire Heah POS0953

you have to use subfield(B2,' ', -1)

Similary if you want last second then use subfield(B2,' ', -2)

Similary if you want last third then use subfield(B2,' ', -3)

Regards
Anand