39361_zh-CN

liliconcom

根据常见变量列表进行更多连接

你好，

 

我想替换此示例中的硬编码列名称：

 

dt = dt1 << Join(
 With( dt_ref ),
 Select(
  :result_Rx_RSSI_,
  :result_snr_Rx_RSSI_,
  :type_,
  :rat_,
  :trx_,
 ),
 SelectWith( :result_Rx_RSSI_ref, :result_snr_Rx_RSSI_ref, ),
 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" )
);

用一些东西开头

common_vars = {"type_", "rat_", "trx};
dt1_only_vars = {"result_Rx_RSSI_", "result_snr_Rx_RSSI_"};
dt_ref_only_vars = {"result_Rx_RSSI_ref", "result_snr_Rx_RSSI_ref"};

... 这应该导致相同的结果。

 

我怎么做？

 

谢谢您的帮助！

Re：根据常见变量列表进行更多连接谢谢！这比我的方法更容易阅读。Re：根据常见变量列表进行更多连接

也许我过分简化了你的情况，但是，仅供参考，这也有效：

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

Re：根据常见变量列表进行更多连接

感谢您的反馈。

 

这是我通过这篇博客文章解决它的方式：

 

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

 

common_vars = {"type_", "rat_", "trx"};
dt1_only_vars = {"result_Rx_RSSI_", "result_snr_Rx_RSSI_"};
dt_ref_only_vars = {"result_Rx_RSSI_ref", "result_snr_Rx_RSSI_ref"};

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

Re：根据常见变量列表进行更多连接

嗨，亚历克斯！

 

首先，感谢您使您的示例比原始问题更难打字！:)  

 

我不清楚你为什么要使用一个公共变量列表进行连接。 变量会改变吗？用户对话会有用吗？你的理由可能会影响你应该采取的方向。

 

尽管如此，让我们考虑有人必须在你的无分数数据集中维护“参考值”，并且记录No Country数据集值没有No Score值的情况会有所帮助...这将表明Left Outer Join将是适当。

 

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

如果您确实需要，可以轻松地将变量转换为可以随意评估和替换的表达式。

Re：根据常见变量列表进行更多连接

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

Re：根据常见变量列表进行更多连接嗨，亚历克斯！

有很多方法可以做到这一点。

你可以发布一些（匿名的，如果必要的话）dt1和dt_ref数据集吗？