topic Re: How to reference a list of n columns when joining data tables? in Discussions

How to reference a list of n columns when joining data tables?

GraysonC — Tue, 16 Jul 2024 17:37:09 GMT

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.

Re: How to reference a list of n columns when joining data tables?

jthi — Tue, 16 Jul 2024 17:43:03 GMT

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")
);

Re: How to reference a list of n columns when joining data tables?

hogi — Wed, 17 Jul 2024 19:53:31 GMT

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")
);

Re: How to reference a list of n columns when joining data tables?

GraysonC — Tue, 16 Jul 2024 22:52:54 GMT

I had no idea about the Eval command. Since I had the columns as strings in a list, I just added Eval(cycle_num) like you did and it worked! I was way overcomplicating the solution. Thank you so much for the help!