Column value format based on row description

[Amy Haymes]

I am trying to replace a process currently done in excel. Currently, the column values represent a mixture of percentages and whole numbers based on the row description.

So long as the information is viewed in excel, this can be maintained. Once it is loaded into spotfire, the formatting is lost and everything in that column becomes a Real data type.

I have written case statements in calculated columns to transforms the values that need to be a % and those that need to be a number, however, I end up with two colums and I need to oly have one column, where the data type of the value is determined by the row description.

 

I have added an image to hopefully give clarity. Any help is appreciated.

[Fabian Duerr]

Spotfire is not Excel. You are not working with spreadsheets here.Each column can only hold ONE data type. Instead of splitting your column you could add an additional column that holds the type of unit. I would even suggest du split your entire table into two tables... But the questions is where you want to go with your data What is your final goal

[Amy Haymes]

Thanks for the reply. I fully understand Spotfire is not Excel and it is not set up to hold multiple datatypes in a column. The final goal is to provide the customer with the data table as they requested it whether I agree with their structure or not. I can concatonate the two split columns and give them the readability they are asking for even though it converts the data type to string.

 

IF there is a responder who knows how to over ride the good sense of the software and force two data types into one column please let me know.