cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar

Match columns on table update using string

Hello,

I'm trying to update one data table with columns from another table, using matching columns criteria defined by a string.

In the example below, dt1 is my main table, and dt2 is a sort of config file containing the column to add (i.e. "ROUTE"), while all other columns are used in matching to dt1. Since dt2 is user-defined, I can't know these other column names ahead of time, hence the need to use a formatted string:


// define string for column matching

textinp = "";


matchCols = dt2 << get column names(string);


// remove "ROUTE" from dt2 column name list

for (i = nitems(matchCols), i > 0, i--,

    if (contains(matchCols, "ROUTE"), remove from(matchCols, i));

);


// create string for column matching
for (i=1, i<=nitems(matchCols), i++,

  textinp = concat(textinp,":",matchCols," = :",matchCols,", ");

);


textinp = left(textinp,length(textinp)-2);

print(textinp);

// update dt1

dt1 << Update(

  With( dt2 ),

  Match Columns(

  parse(textinp)

  )

);


Parsing textinp to the update command produces this error (the string is produced correctly as seen in the first line):


":X = :X, :Y = :Y"

Unexpected ",". Perhaps there is a missing ";" or ",".

Line 1 Column 8: :X = :X►, :Y = :Y

As far as I know there shouldn't be any problem separating multiple columns with commas inside match columns(), and in fact when I try to define it literally, i.e.:

// update dt1

dt1 << Update(

  With( dt2 ),

  Match Columns(

  :X = :X, :Y = :Y

  )

);


It works perfectly fine. Any idea why it can't handle / parse the string? Your help is greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Match columns on table update using string

Match Columns() apparently accepts two lists of column names (not as strings) as its matching argument.

Try this:

// Example tables

dt2 = Open("$SAMPLE_DATA/Big Class.jmp");

dt1 = dt2 << subset(selected columns(0), sampling rate(0.5));

dt1 << delete columns(name);

// define list for column matching

matchCols = dt2 << get column names();

Remove From(matchCols, Contains(matchCols, Expr(name)));

// update dt1

dt1 << Update(With(dt2), Match Columns(matchCols = matchCols));


View solution in original post

3 REPLIES 3
pmroz
Super User

Re: Match columns on table update using string

This should work:

update_expr = evalinsert(

"dt1 << Update(

With( dt2 ),

Match Columns(^textinp^)

)");

eval(parse(update_expr));

ms
Super User (Alumni) ms
Super User (Alumni)

Re: Match columns on table update using string

Match Columns() apparently accepts two lists of column names (not as strings) as its matching argument.

Try this:

// Example tables

dt2 = Open("$SAMPLE_DATA/Big Class.jmp");

dt1 = dt2 << subset(selected columns(0), sampling rate(0.5));

dt1 << delete columns(name);

// define list for column matching

matchCols = dt2 << get column names();

Remove From(matchCols, Contains(matchCols, Expr(name)));

// update dt1

dt1 << Update(With(dt2), Match Columns(matchCols = matchCols));


Re: Match columns on table update using string

Thank you both! Only tried the 2nd answer, which worked!