Get ready for a doozy.

Setup

SharePoint lists allow for multiple values which can be quite a headache once they have been exported out of SharePoint. This can give you nasty looking values like this:

  • Well 1 (14638);#1104;#Well 2;#1355;#Well 3;#1151;#Well 4;#1299

Furthermore, Spotfire doesn’t have a nice way to break apart these multiple values. PowerBI actually does this to some extent, but for us Spotfire practitioners the only way to remedy this is in R. We’ll need to break apart the rows for each entry and to simplify this, we’ll just duplicate everything else.

In our Spotfire analysis, we’ll have:

  1. the imported SharePoint list as one table
  2. the output table
  3. one data function

Data Function

We won’t need any special package this time as even baseR can do this. Here is the script:

Parameters:

  • Input: a table named “df” with all allowed integers
  • Output: a table named “output”

You’ll notice some Egyptian runes in out strsplit function; this ominous script is regex which we will deconstruct in the next section.

Regex

Now we’ll head over to regexr.com to help me figure out regex. It is by far the nicest tester out there.

Check it out here: http://regexr.com/3fc3r, I’ll out the explain expression below:

*     Star. Match 0 or more of the preceding token.

;#     Character. Matches a “#” character (char code 35).

(     Capturing group #1. Groups multiple tokens together and creates a capture group for extracting a substring or using a backreference.

\d     Digit. Matches any digit character (0-9).

*         Star. Match 0 or more of the preceding token.

;#     Character. Matches a “;#”

|         Alternation. Acts like a boolean OR. Matches the expression before or after the |.

\d     Digit. Matches any digit character (0-9).

*         Star. Match 0 or more of the preceding token.

)

We needed to make a group because there were two situations to look for:

  1. ;#number;#
  2. And ;#number, the end piece

Let me know if you have any questions!

Written by Lucas Wood
I am unable to explain to my family and friends what I do for a living. ---DataShopTalk, editor. Exchange.ai, admin. Ruths.ai, data analyst.