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

[Karl Street]

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

[Gaia Paolini]

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)

[Karl Street]

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 

 

Thanks 

[Gaia Paolini]

A simple solution would be:

for the total thickness:

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

for the numeric conversion:

DenseRank([lithology])

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.