topic Re: Match columns on table update using string in Discussions

Match columns on table update using string

rhakim_gmail_co — Thu, 25 Aug 2016 11:30:03 GMT

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!

Re: Match columns on table update using string

pmroz — Thu, 25 Aug 2016 12:57:32 GMT

This should work:

update_expr = evalinsert(

"dt1 << Update(

With( dt2 ),

Match Columns(^textinp^)

)");

eval(parse(update_expr));

Re: Match columns on table update using string

ms — Thu, 25 Aug 2016 13:35:50 GMT

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

rhakim_gmail_co — Sat, 27 Aug 2016 12:55:05 GMT

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