Subscribe Bookmark RSS Feed

error: "Expression does not fit available column types"

eveyja

Community Trekker

Joined:

Mar 8, 2016

Hi everyone,

In the following code I get the error: "Expression does not fit available column types" , and it happens during the formula evaluation..

I'm just trying to reference the same table as I am adding a new column to, such as subtracting two columns within the table called "dt" that I am adding a new column to... Can't figure out why it's not working.

dt = myTable();

cols = dt << GetColumnNames("String");

//Wait(3);

For(c=1, c<=NItems(cols), c++,

  If( StartsWith(cols, "HTOL"),

  dt << New Column(cols || " Δ", Numeric, Continuous, Formula( eval list( column(dt,4)) ) );

  );

);

thanks

jeff

5 REPLIES
Phil_Brown

Super User

Joined:

Mar 20, 2012

Jeff,

You are correct the problem is "eval list( column(dt,4) )". Eval List() expects there to be a list to be evaluated. for example: {1,2,3}.

Is this what you intended? Are the contents of Column(dt, 4) lists? Since you most likely have numeric values in that column, an error is thrown.

Even then, JMP data table columns only accept 3 data types, Numeric, Character and Rowstate. If you indeed had lists in every cell of Column(4), they would be actually strings. i.e. "{1,2,3}". In that case one would first have to Parse the string to convert it to an expression, and THEN EvalList() would work.

The question is, what did you intend that formula to do?

PDB
eveyja

Community Trekker

Joined:

Mar 8, 2016

thanks for the quick response!!

I have a table "dt" which has data columns.  They can be "data 0" or "data 100" or "data 200" , up to some unknown number.

Whatever the column is called, I want to subtract the "data 0" column from it - even if the column is "data 0" (so that column is all zeroes, but the others like "data 100" aren't all zeroes".

So I was trying to have a for loop that would check each column, and if it is named "data 100" for example, I wanted it to automatically create a "data 100 delta" column, with the formula in it : (data 100 - data 0).

I thought I could do that as follows:

dt = myTable();

cols = dt << GetColumnNames("String");

//Wait(3);

For(c=1, c<=NItems(cols), c++,

  If( StartsWith(cols, "data"),

  dt << New Column(cols || " delta", Numeric, Continuous, Formula( column(dt,c) - :data 0 ) );

  );

);

the ":data 0" reference seems to work - it will put the ":data 0" data copied into the "data 100 delta" column, but if I put in the "column(dt,c)" , trying to get the data 100 column, it errors out.....

thanks!!

stephen_pearson

Community Trekker

Joined:

Oct 6, 2014

column(dt,c) is not the same as :data 100 as far as the formula is concerned.

One way to get this code to work is to use dynamic JSL, which evaluates the JSL before executing it.

// Not sure if this is quite the right syntx but you get the idea

TargetColumn = column(dt,c) <<Get Name("String");

//dynamic replacement to go inside the if clause

Eval(

  Parse(

  Eval Insert(

  "\[

dt << New Column(cols || " delta", Numeric, Continuous, Formula( :^TargetColumn^ - :data 0 ) );

  ]\"

  )  )  );

If your column names have special characters in you may need to use :Name("^TargetColumn^") in the formula. This can still give odd results if there are ^/-+* symbols in the column name as the formula doesn't evaluate reliably.

pmroz

Super User

Joined:

Jun 23, 2011

Similar technique as @stephen.pearson:

dt = New Table( "Demo", Add Rows( 3 ),

      New Column( "data 0", Numeric, Continuous, Format( "Best", 12 ), Set Values( [1, 2, 3] ) ),

      New Column( "data 100", Numeric, Continuous, Format( "Best", 12 ), Set Values( [4, 5, 6] ) ),

      New Column( "data 200", Numeric, Continuous, Format( "Best", 12 ), Set Values( [7, 8, 9] ) )

);

cols = dt << get column names("String");

for (c = 1, c <= nitems(cols), c++,

    if (starts with(uppercase(cols[c]), "DATA"),

        new_col_expr = evalinsert("\[dt << new column( cols || " Δ", Numeric, Continuous, Formula(:^cols^ - :data 0))]\");

        eval(parse(new_col_expr));

    );

);

eveyja

Community Trekker

Joined:

Mar 8, 2016

Thanks PMroz! I ended up solving this one last night using verbage pretty similar to yours - I used something like this:

I think I used parse .. I don't remember if I had to use eval or not , I am about 60% sure I did.

If anyone needs me to , reply to this and I'll hunt up the exact syntax.

dt << new column( parse(cols || " Δ"), Numeric, Continuous, Formula(:^cols^ - :data 0))