topic LOAD statement, changing a value from a sharepoint list in QlikView

LOAD statement, changing a value from a sharepoint list

Sun, 06 Mar 2011 20:34:29 GMT

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!

LOAD statement, changing a value from a sharepoint list

Miguel_Angel_Baeyens — Sun, 06 Mar 2011 21:15:34 GMT

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.

LOAD statement, changing a value from a sharepoint list

Sun, 06 Mar 2011 21:27:23 GMT

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.

LOAD statement, changing a value from a sharepoint list

Miguel_Angel_Baeyens — Sun, 06 Mar 2011 21:41:37 GMT

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?

LOAD statement, changing a value from a sharepoint list

Sun, 06 Mar 2011 21:54:24 GMT

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?

LOAD statement, changing a value from a sharepoint list

Miguel_Angel_Baeyens — Sun, 06 Mar 2011 21:58:54 GMT

Yes, now it seems clearer:

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

Hope that helps.

LOAD statement, changing a value from a sharepoint list

Sun, 06 Mar 2011 22:11:03 GMT

Thank you thank you thank you thank you!!!

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