Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level III

## making a join more based on a list of common vars

Hello,

I would like to replace the hard coded column names in this example:

```dt = dt1 << Join(
With( dt_ref ),
Select(
:type_,
:rat_,
:trx_,
),
By Matching Columns(
:type_ = :type_,
:rat_ = :rat_,
:trx_ = :trx_,
),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 ),
Output Table( "merged_results" )
);```

with something that starts with

```common_vars = {"type_", "rat_", "trx};

... and this should lead to the same result.

How do I do that?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Level III

## Re: making a join more based on a list of common vars

Thank you for the feedback.

This is how I solved it thanks to this blog entry:

http://www.pega-analytics.co.uk/blog/handling-lists-of-columns/

``````common_vars = {"type_", "rat_", "trx"};

col_names = dt1_only_vars || common_vars;

match_str = "";
For( i = 1, i <= N Items( common_vars ), i++,
match_col_name = common_vars[i];
mstr = Eval Insert( ":^match_col_name^ = :^match_col_name^," );
match_str = match_str || mstr;
);

// this is UGLY but I do not know any other way (yet?)
dt_str = Eval Insert(
"dt = dt1 << Join(
With( dt_ref ),
Select(
Eval( col_names )

),
SelectWith( Eval(dt_ref_only_vars) ),
By Matching Columns(
^match_str^
),

Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 ),
Output Table( \!"merged_results\!" ));"
);
Eval( Parse( dt_str ) );``````
6 REPLIES 6
Highlighted
Level VI

## Re: making a join more based on a list of common vars

Hi, Alex!

There are many ways to do that.

Can you post some (anonymized, if necessary,) dt1 and dt_ref datasets?
Highlighted
Level III

## Re: making a join more based on a list of common vars

``````dt = Open( "\$SAMPLE_DATA/Decathlon.jmp" );

dt_no_score = dt << Subset(
Output Table( "Decathlon_no_score" ),
All rows,
columns(
:Name,
:Country,
:Name( "100m" ),
:Long Jump,
:Shot Put,
:High Jump,
:Name( "400m" ),
:Name( "100m hurdles" ),
:Discus,
:Pole Vault,
:Javelin,
:Name( "1500m" )
)
);

dt_no_country = dt << Subset(
Output Table( "Decathlon_no_country" ),
All rows,
columns(
:Name,
:Score,
:Name( "100m" ),
:Long Jump,
:Shot Put,
:High Jump,
:Name( "400m" ),
:Name( "100m hurdles" ),
:Discus,
:Pole Vault,
:Javelin,
:Name( "1500m" )
)
);

dt_recombined = dt_no_country << Join(
With( dt_no_score ),
Select(
:Name,
:Score,
:Name( "100m" ),
:Long Jump,
:Shot Put,
:High Jump,
:Name( "400m" ),
:Name( "100m hurdles" ),
:Discus,
:Pole Vault,
:Javelin,
:Name( "1500m" )
),
SelectWith( :Country ),
By Matching Columns(
:Name = :Name,
:Name( "100m" ) = :Name( "100m" ),
:Long Jump = :Long Jump,
:Shot Put = :Shot Put,
:High Jump = :High Jump,
:Name( "400m" ) = :Name( "400m" ),
:Name( "100m hurdles" ) = :Name( "100m hurdles" ),
:Discus = :Discus,
:Pole Vault = :Pole Vault,
:Javelin = :Javelin,
:Name( "1500m" ) = :Name( "1500m" )
),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 )
)``````
Highlighted
Level VI

## Re: making a join more based on a list of common vars

Hi, Alex!

First, thank you for making your example much more difficult to type than your original question!  :)

I'm unclear on why you wish to use a list of common variables for a join.  Do the variables change?  Would User Dialogs be useful?  Your reasons may influence the direction you should take.

Nonetheless, let's consider that someone will have to maintain the "reference values" in your No Score dataset, and it would be helpful to record where No Country dataset values have no No Score values...that would indicate a Left Outer Join would be appropriate.

``````dt_recombined = Query(
Table( dt_no_country, "t1" ),
Table( dt_no_score, "t2" ),
"SELECT t1.Name AS [Name from Table1], t1.Score, t1.[100m], t1.[Long Jump], t1.[Shot Put], t1.[High Jump], t1.[400m], t1.[100m hurdles], t1.Discus, t1.[Pole Vault], t1.Javelin, t1.[1500m],
t2.Name AS [Name from Table2], t2.Country
FROM t1
LEFT OUTER JOIN t2
ON  ( t1.Name = t2.Name and
t1.[100m] = t2.[100m] and
t1.[Long Jump] = t2.[Long Jump] and
t1.[Shot Put] = t2.[Shot Put] and
t1.[High Jump] = t2.[High Jump] and
t1.[400m] = t2.[400m] and
t1.[100m hurdles] = t2.[100m hurdles] and
t1.Discus = t2.Discus and
t1.[Pole Vault] = t2.[Pole Vault] and
t1.Javelin = t2.Javelin and
t1.[1500m] = t2.[1500m]  ) " ); ``````

If you really need, you can easily turn the variables into expressions you can evaluate and substitute at will.

Highlighted
Level III

## Re: making a join more based on a list of common vars

Thank you for the feedback.

This is how I solved it thanks to this blog entry:

http://www.pega-analytics.co.uk/blog/handling-lists-of-columns/

``````common_vars = {"type_", "rat_", "trx"};

col_names = dt1_only_vars || common_vars;

match_str = "";
For( i = 1, i <= N Items( common_vars ), i++,
match_col_name = common_vars[i];
mstr = Eval Insert( ":^match_col_name^ = :^match_col_name^," );
match_str = match_str || mstr;
);

// this is UGLY but I do not know any other way (yet?)
dt_str = Eval Insert(
"dt = dt1 << Join(
With( dt_ref ),
Select(
Eval( col_names )

),
SelectWith( Eval(dt_ref_only_vars) ),
By Matching Columns(
^match_str^
),

Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 ),
Output Table( \!"merged_results\!" ));"
);
Eval( Parse( dt_str ) );``````
Highlighted
Staff

## Re: making a join more based on a list of common vars

Perhaps I have oversimplified your case but, FYI, this also works:

``````dt1 = NewTable("One",
NewColumn("A", Values([1,2])),
NewColumn("B", Values([3,4])),
NewColumn("K1", Values([5,6])),
NewColumn("K2", Values([7,8]))
);

dt2 = NewTable("Two",
NewColumn("C", Values([9,10])),
NewColumn("D", Values([11,12])),
NewColumn("K2", Values([5,6])),
NewColumn("K1", Values([7,8]))
);

dt1KeyCols = {"K1", "K2"};
dt2KeyCols = {"K2", "K1"};
dtFinal = dt1 << Join(
With(dt2),
By Matching Columns( dt1KeyCols = dt2KeyCols ),
Output Table( "Joined" )
);``````
Highlighted
Level III

## Re: making a join more based on a list of common vars

Thank you! This is much easier to read than my approach.
Article Labels

There are no labels assigned to this post.