topic Re: Join two tables with variable names in Discussions

Join two tables with variable names

AT — Tue, 27 Mar 2018 23:19:49 GMT

Hi,

I like to join two tables with variable names. Here is the script.

Eval(
	Parse(
		"s3 << Join(
With( rds),
Merge Same Name Columns,
By Matching Columns(
" || name3 || ",
" || name4 ||
		"
),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
);"
 
	)
);

I get an error " Name Unresolved: Red LED Hue x 64{1} in access or evaluation of "Red LED Hue 64/*###*/. No issue with name3 "Serail#" but issue with "Red LED Hue x 64[1]".

Here is the scrip that works fine but I like to replace the variables names with name3 and name4.

Data Table( "S3" ) << Join(
	With( Data Table( "RDS" ) ),
	Merge Same Name Columns,
	By Matching Columns( :Serial# = :Serial#, :Name( "Red LED Hue x 64[1]" ) = :Name( "Red LED Hue x 64[1]" ) ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 )
)

I appreciate your help. Thanks

Re: Join two tables with variable names

cwillden — Tue, 27 Mar 2018 18:43:07 GMT

how are you defining name3 and name4? Can you post that part of your script as well as show how those variables evaluate in the log? (e.g. run "Show(name3, name4)")

Re: Join two tables with variable names

AT — Tue, 27 Mar 2018 23:21:09 GMT

Thanks Cameron. The name3 and name4 are listed below when show(name) is invoked.

name3 = ":Serial# = :Serial#";
name4 = ":Red LED Hue x 64[1] = :Red LED Hue x 64[1]";
The name 3 and name4 are created by using the following script:xvar = .;
yvar = .;
win = New Window( "Choose Two Variables for Matching",
	<<Modal,
	<<On Validate(
// require the user to select two variables before clicking OK
		Show( xvar, yvar );
		If( Is Missing( xvar ) | Is Missing( yvar ),
			0, // if xvar or yvar are missing, do nothing when OK is clicked
			1
		);
	),
	Text Box( " Select two numeric columns. " ),
	H List Box(
		Text Box( " Choose First " ),
		x = Col List Box(
			dt1, // data table reference
			all, // display all columns from the data table
			xvar = (x << Get Selected)[1];
// get the name of the selected column before the window closes
			Show( xvar );
		),
		Text Box( "Choose Second" ),
		y = Col List Box(
			dt2,
			all,
			yvar = (y << Get Selected)[1];
			Show( yvar );
		)
	)
);
 
xcol = Column( dt1, xvar ); // get the columns
ycol = Column( dt2, yvar );
 
 
name1 = ":" || xvar;
name2 = ":" || yvar;
name3 = name1 || " = " || name2;
Show( name3 );

Similarily for name4.

I appreciate your help. Thanks

Re: Join two tables with variable names

cwillden — Tue, 27 Mar 2018 19:30:02 GMT

The problem is almost certainly with the square brackets in Name4. If column names have special characters, then you cannot use the colon(:) with the column name. You need to get the column name string wrapped in :Name().

Try this:

name1 = ":Name(" || xvar || ")";
name2 = ":Name(" || yvar || ")";
name3 = name1 || " = " || name2;

Re: Join two tables with variable names

AT — Tue, 27 Mar 2018 20:27:15 GMT

Thanks Cameron. I modified name3 and name4 like what you suggested and I got an error:

Name arg must be quoted string

Line 5 Column 9: :Name(►Serial#) = :Name(Serial#),

If I keep name3 like before, no error for Serial# but for name4. I don't know a priori what users chooses for name3 and name4.

Thanks for your continuous support.

Re: Join two tables with variable names

cwillden — Tue, 27 Mar 2018 22:19:43 GMT

Oops, you're missing quotes inside of name. Forgot about that. You can do that several ways, but here's one where we use escape syntax to insert double quotes inside of a string.

name1 = ":Name(\!"" || xvar || "\!")";
name2 = ":Name(\!"" || yvar || "\!")";
name3 = name1 || " = " || name2;

You should definitely use the :Name("col name") approach to keep this robust since you will need it to handle situations with special characters in the column names.

Re: Join two tables with variable names

AT — Tue, 27 Mar 2018 23:38:09 GMT

Thanks Cameron. I tried it and work fine. Thanks again.