topic Use a variable number of columns to join data tables in Discussions

Use a variable number of columns to join data tables

ehchandlerjr — Mon, 04 Dec 2023 19:36:20 GMT

Hello - I'm trying to do a join as part of a script. However, the "select" and "selectwith" parts are messing me up. I'm dynamically selecting columns of linked data tables earlier in the script, and and passing them as a list to the "select" and "select with" parts. I tried Eval(Eval Expr(Expr())), but that didn't work. I think I'm just misunderstanding the way those two actually take information.

Data Table(dt2b) << Join(
    With(Data Table("Experimental Info")),
    Match Flag(1),
    Select(selectedCols1String),
    SelectWith(selectedCols2String),
    By Matching Columns(:ID = :ID),
    Drop multiples(0, 0),
    Include Nonmatches(0, 0),
    Preserve main table order(1)
);

The output for selectedCols1String and selectedCols2String look like this: ":ID, :2Theta, :Data, :Error, :Raw Data, :Raw Error"

Thanks!

Re: Use a variable number of columns to join data tables

jthi — Mon, 04 Dec 2023 19:42:17 GMT

You shouldn't use a string but rather a list and use Eval with it

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Trial1.jmp");
dt2 = Open("$SAMPLE_DATA/Little.jmp");

select_cols = {"popcorn", "oil amt", "batch", "yield"};
with_cols = {"yield", "batch"};

dt3 = dt << Join(
	With(dt2),
	Select(Eval(select_cols)),
	SelectWith(Eval(with_cols)),
	By Matching Columns(:popcorn = :popcorn, :batch = :batch, :oil amt = :oil)
);

Here I'm using list of strings, but those could be list references also

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Trial1.jmp");
dt2 = Open("$SAMPLE_DATA/Little.jmp");

select_cols = {:popcorn, :oil amt, :batch, :yield};
with_cols = {:yield, :batch};

dt3 = dt << Join(
	With(dt2),
	Select(Eval(select_cols)),
	SelectWith(Eval(with_cols)),
	By Matching Columns(:popcorn = :popcorn, :batch = :batch, :oil amt = :oil)
);

Re: Use a variable number of columns to join data tables

ehchandlerjr — Mon, 04 Dec 2023 20:02:18 GMT

Thank you so much!