I am fairly new to JSL and have very basic knowledge on coding in general, so I may not know all of the proper terms to describe my problem, so I apologize in advance.
I want to automate a process I do regularly where I combine and transform 2 data tables. I already have a script that can do this, but I am trying to figure out how to have JMP reference columns instead of having them hard-coded because the column references can change. For example, this is a section of the code that does what I want already, but is hard-coded:
(Data Table( baselined_dt ) <<
Join(
With( Data Table( main_dt ) ),
Select( :SampleId, :ReplicateId ),
SelectWith(
dlg["data_list"], :ReplicateId // Lets user select columns//
),
Select(
:Dye, :Type, :"1"n, :"2"n, :"3"n, :"4"n, :"5"n, :"6"n, :"7"n, :"8"n, :"9"n,
:"10"n, :"11"n, :"12"n, :"13"n, :"14"n, :"15"n, :"16"n, :"17"n, :"18"n,
:"19"n, :"20"n, :"21"n, :"22"n, :"23"n, :"24"n, :"25"n, :"26"n, :"27"n,
:"28"n, :"29"n, :"30"n, :"31"n, :"32"n, :"33"n, :"34"n, :"35"n, :"36"n,
:"37"n, :"38"n, :"39"n, :"40"n, :"41"n, :"42"n, :"43"n, :"44"n, :"45"n
),
By Matching Columns( :ReplicateId = :ReplicateId ),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 )
)) << Set Name( "Joined Data Table" );
The problem is the number of :"#"n is not always 1-45, and can sometimes be 1-40. Right now I just delete 41-45 in the code if that's the case, but I want a way to automate this so I don't have to change the code every time. I have been able to create a list that contains {"1"n, "2"n, "3"n, etc}, or a list that contains {":1", ":2", ":3", etc}, but I am not sure if that is even needed for what I am trying to do.
Essentially, my limited code knowledge thinks a For loop within the "Join" function can accomplish this, but I haven't found a way to make it work. I also don't even know if a For loop is possible within the "Join" function. My thought process has the solution looking something like this:
cycle_list = {};
For( i = 1, i <= N Items(cycle_num), i++,
y1 = cycle_num[i];
y2 = (":" || y1);
InsertInto(cycle_list, y2)
);
(Data Table( baselined_dt ) <<
Join(
With( Data Table( main_dt ) ),
Select( :SampleId, :ReplicateId ),
SelectWith(
dlg["data_list"], :ReplicateId // Lets user select columns//
),
Select(
:Dye, :Type,
For( i = 1, i <= N Items( cycle_list ), i++,
cycle_list[i];
);
),
By Matching Columns( :ReplicateId = :ReplicateId ),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 )
)) << Set Name( "Joined Data Table" );If there is a reference to this somewhere in either the forums or scripting index, I either can't find a similar discussion about it, or I may just not know how to phrase what I am trying to do correctly.
Usually in cases like this you can just use Eval() if you have list of your columns
Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Trial1.jmp");
dt2 = Open("$SAMPLE_DATA/Little.jmp");
collist = {:popcorn, :oil amt, :batch, :yield};
dt_joined = dt << Join(
With(dt2),
Select(Eval(collist)),
SelectWith(:yield),
By Matching Columns(:popcorn = :popcorn, :batch = :batch, :oil amt = :oil),
Output Table("Join table")
);
It will also work if you have columns as strings (this is the way I do it)
Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Trial1.jmp");
dt2 = Open("$SAMPLE_DATA/Little.jmp");
collist = {"popcorn", "oil amt", "batch", "yield"};
dt_joined = dt << Join(
With(dt2),
Select(Eval(collist)),
SelectWith(:yield),
By Matching Columns(:popcorn = :popcorn, :batch = :batch, :oil amt = :oil),
Output Table("Join table")
);
Or via Name Expr() - which is kind or sqrt(Eval())
Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Trial1.jmp");
dt2 = Open("$SAMPLE_DATA/Little.jmp");
collist = {"popcorn", "oil amt", "batch", "yield"};
dt_joined = dt << Join(
With(dt2),
Select(Name Expr(collist)),
SelectWith(:yield),
By Matching Columns(:popcorn = :popcorn, :batch = :batch, :oil amt = :oil),
Output Table("Join table")
);
Hm, even Print(collist); collist works ... and the collist gets printed. So, it seems that the argument gets evaluated.Why the hell doesn't Select(collist) work?
dt_joined = dt << Join(
With(dt2),
Select(Print(collist);collist),
SelectWith(:yield),
By Matching Columns(:popcorn = :popcorn, :batch = :batch, :oil amt = :oil),
Output Table("Join table")
);
