String parsing problem

Hello community,

I'm using spotfire version 7.0.0.  I've got a long string that comes in as a 10x1 matrix.  Examples:

[0.013648; (-0.030076); 0.0247245; 0.1699055; (-0.038511); 0.2042657; (-0.0891361); (-0.232781); 0.2746771; 0.0961958]

[(-0.00045739883008); 0.0053886308992; 0.07407614944; (-0.209323777129216); (-0.192192822604288); (-0.136876310663168); 0.117294202979328; 1.2710778671319; 0.05503196268032; (-0.84809125202125)]

[0.046466666666667; 0.0098666666666667; 0.048816666666667; (-0.211796666666667); 1.34277; (-0.14344); (-0.74070333333333); 0.96302333333333; 0.36229; (-0.042993333333333)]

[0.00097; 0.01669; 0.271374; 0.614217; (-0.286766); 0.245168; (-0.277355); (-0.083502); (-0.290052); (-1.406247)]

I've been trying to accomplish with a calculated column using the RXReplace function.  I have been able to use RXReplace to strip out the brackets, parentheses and semicolons to get down to this:

 0.013648 -0.030076 0.0247244 0.1699056 -0.0385112 0.2042656 -0.089136 -0.2327812 0.2746772 0.0961956

0.03450433083392 0.00910961235968 0.089010010097664 -0.19783756048384 -0.073182952592384 -0.077119854939136 -0.164117494716416 1.02202082362163 -0.185890537460736 -0.143515771483136

0.018494976 -0.035660832 0.6221038912 0.7645438528 -1.5033110656 0.1361690048 -0.4071770048 2.3226135552 -0.0517067296 -0.9707546912

Ultimately, I want to parse these values into a single column of 10 rows.  (value can be trimmed down to 4 decimal places ~ ##.####)   Each string is already populated into 10 rows.  What I need to do now is just pick out the individual values I want from each row.  I have another column that defines the position in the string of the value I want.    

example

Data name Value String data value (calculated column)
Data ID 1 1 [##.##; ##. ##; ….] ##.####
Data ID 2 2 [##.##; ##. ##; ….] ##.####
Data ID 3 3 [##.##; ##. ##; ….] ##.####
Data ID 4 4 [##.##; ##. ##; ….] ##.####
Data ID 5 5 [##.##; ##. ##; ….] ##.####
Data ID 6 6 [##.##; ##. ##; ….] ##.####
Data ID 7 7 [##.##; ##. ##; ….] ##.####
Data ID 8 8 [##.##; ##. ##; ….] ##.####
Data ID 9 9 [##.##; ##. ##; ….] ##.####
Data ID 10 10 [##.##; ##. ##; ….] ##.####

I've been working with variations of this:  

RXReplace([column with stripped down matrix],"\\(([0-9]*\\.[0-9]*) ([0-9]*\\.[0-9]*) ([0-9]*\\.[0-9]*) ([0-9]*\\.[0-9]*) ([0-9]*\\.[0-9]*) ([0-9]*\\.[0-9]*) ([0-9]*\\.[0-9]*) ([0-9]*\\.[0-9]*) ([0-9]*\\.[0-9]*) ([0-9]*\\.[0-9]*)\\)$","$[Value]","")  

I have tried multiple variations of regular expression for arg2.  I think the issue might be the negative values.  Though I have tried expressions which should accept any leading character.

Thanks for any help in advance.  Yes, I know column splitting in version 7.6+ would solve this issue.

 

 

 

(1) Answer

Login