LOAD statement, changing a value from a sharepoint list

[#1]

Hi,
I have a sharepoint list I am importing and want to manipulate the input to Qlikview via the LOAD statement.

The field contains a value in the following format: "Last Name, First Name (Texas)"
e.g. "Ericsson, Bob (Texas)"

I remove Texas with:
TextBetween(ows_AssignedTo, '#', ' ('Texas'),

But now I want to "flip it", so that the first name is shown first - and without a comma!
e.g. "Bob Ericsson"

Can someone please help me with how this could be done?

Thanks!

[Miguel_Angel_Baeyens]

Hello,

If all records have "(Texas)" in the record name, the following might work:

=SubField(Replace('Ericsson, Bob (Texas)', '(Texas)', ''), ', ', 2) & SubField(Replace('Ericsson, Bob (Texas)', '(Texas)', ''), ', ', 1)

Hope that helps.

[#3]

Hi Miguel,
Thanks for replying!

I get an error message;

Error in expression:
Replace takes 3 parameters
row:
LOAD ows_ID,
SubField(Replace(FieldName, '#', ' (Texas)', ''), ', ', 2) & SubField(Replace(FieldName, '#', ' (Texas)', ''), ', ', 1),


PS. as you can see the values also includes a "#"... DS.

[Miguel_Angel_Baeyens]

Hi,

I don't see the "#" in your example above. So the following should work:

=SubField(Replace(FieldName, '(Texas)', ''), ', ', 2) & SubField(Replace(FieldName, '(Texas)', ''), ', ', 1)

Am I missing something?

[#5]

Sorry, I should have given you a better example.

This is how the output from sharepoint look like unmodified:

242;#Last, First (Texas)

263;#Last, First (Texas) 288;#Last, First (Texas)

Again, sorry! Does this make better sense?

[Miguel_Angel_Baeyens]

Yes, now it seems clearer:

=SubField(TextBetween('242;#Last, First (Texas)', '#', '(Texas)'), ', ', 2) & SubField(TextBetween('242;#Last, First (Texas)', '#', '(Texas)'), ', ', 1)

Hope that helps.

[#7]

Thank you thank you thank you thank you!!!

(Hade to add a & ' ' to get the space, but works awesome!)