Find and replace commas with spaces in Spotfire

[Avi Levy]

Hi All,

 

I hope you all are healthy in this current climate.

 

I have question I'd really like some help with: Essentially, I have a spotfire table that has many rows and columns, some of which are string. This data table will need to be exported in CSV for a different application entirely (sending surveys to recipients) and in the other program when you upload the data it needs to be in CSV.Therefore, some columns cannot have commas in them when exported from Spotfire.

The columns with commas are

Study Name

City

Site Name

Full Name

Is there a way of creating a formula that would go through each column, find the commas, and then replace them with a space or even remove them Alternatively, a calculated column that would give a "yes" for anything with a comma in the cell would be great too as I can then filter on them.

 

Example 1:

Full Name

John, Smith

 

Example 2:

Study Name:

Small Cell Lung Cancer, Oncology

 

Thanks!

[Fabian Duerr]

If you just have a few columns you can use either new calculated columns or the transformation option to recalculate and replace the input column. The spotfire function you are looking for is RXreplace:

RXReplace([your.column],",", " ","i")You can also use a data function that takes your entire table (df) as input and then returns a new table (df.new):

df.new

[Avi Levy]

Hi fabd,

 

Thank you so much for this quick answer (for some reason I didn't get a notification so just saw this).

 

This might be silly, but would I then have to create say 5 new calculated columns withRXReplace([your.column],",", " ","i") i.e:

Insert calculation column >RXReplace([study Name],",", " ","i") > new column with no commas. Therefore, I would have duplicates of each column, one with commas and one without

 

Cheers!

[Avi Levy]

Hi fabd, 

 

 

 

Thank you so much for this quick answer (for some reason I didn't get a notification so just saw this).

 

 

 

This might be silly, but would I then have to create say 5 new calculated columns with RXReplace([your.column],",", " ","i") i.e:

 

Insert calculation column > RXReplace([study Name],",", " ","i") > new column with no commas. Therefore, I would have duplicates of each column, one with commas and one without

 

 

 

Cheers!

[Fabian Duerr]

It depends on your approach:

 

If you use calculated columns you will end up with new columns

 

If you use a transformation to calculate and replace the original columns you don't get any additional columns

 

If you use a data function you get a new data table with the same number of columns

[Avi Levy]

OK fantastic, I think I might do the calculated columns option as have no clue about the data function. 

 

Thank you for all your help and fast responses!

[Fabian Duerr]

If you want to get an idea how to do your first data function and maybe also your first document property, you can check out a little tutorial that I posted here:

 

https://community.spotfire.com/questions/self-referential-calculated-column

 

 

[Avi Levy]

Ah nice one - cheers! :D