Create a column based on values on other columns

Question

I am trying to write a script whose values depend on the value of another column.I need to create a new column i.e second column below.Need to assign vales as follows

Sun = 1,Mon = 2 and so on.

(4) => 2 , (6) => 4,5 => 1, 7 => 3

Eg : Sun(5) is 11.How can I script this?

Sat (4)72Sun (5)11Sun (4)12Mon (5)21Mon (4)22Tue (5)31Tue (4)32Wed (5)41Fri (7)63Wed (6)44Thu (7)53Thu (6)54Fri (6)64Sat (7)73

txnelson · Answer

I have attached a data table with your sample data, and my formula.  It runs without error.  You should be able to determine what the differences are in comparison with this data table to find out where the error is coming from

lookups.jmp

ENTHU · Answer

I tried this approach but see the following error -

invalid subscript (must be number or list of numbers) in access or evaluation of 'Subscript'.

My column is character changed it to numeric but doesnt seem to work.

txnelson · Answer

I would just use lists to convert the values.....here is a sample format

parenList = {"", "", "", "2", "1", "4", "3"};
daysList = {"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"};
result = Char( Contains( daysList, Word( 1, :Column 1, " " ) ) ) || parenList[
Num( Word( 2, :Column 1, "()" ) )];