Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

Is there a way to automatically read string text categories and then assign them to an whole number

Karl Street

Recommended Posts

So if you have a series repeating text representing different categories - eg lithology column - sandstone, limestone, siltstone, granite, etc multiple times and you want them to be automitcally converted to a number such that sandstone = 1 & limestone = 2, siltstone = 3 etc What function do you use
Link to comment
Share on other sites

I am not sure I understand when you say 'a series of repeating text'. Do you mean a column in a table, and in Spotfire or another tool When you say convert, do you mean on the same column or creating a calculated column And do these numbers need to be consistent (so e.g if you add another category, the numbers assigned to the pre-existing categories remain the same)
Link to comment
Share on other sites

Sorry should have explained further 


Columns heading could be in the form of - but obviously many more rows 


Epch                     lithology         thickness 


Upper Jurassic      sandstone      5 


Upper Jurassic     siltstone          5 


Upper Jurassic      sandstone      5 


Middle Jurassic     limestone      3 


Middle Jurassic    dolomite        8 


Middle Jurassic    limestone        8 


Middle Jurassic   dolomite       4


So would like to calculate - cumulative  thickness for each lithology reset to 0 for each epoch as a seperate colums 


Convert as a new column each lighology into a numeric index number as a new column 



Link to comment
Share on other sites

A simple solution would be:

for the total thickness:

Sum([thickness]) over ([Epoch],[lithology])

for the numeric conversion:


this would change if you introduce a new value of lithology, as it simply ranks the values in alphabetical order.

Let me know if the requirements were different.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...