44083_zh-CN

liliconcom

难以遍历两列以创建第三列

 

dtt=Data table("inputdata");
//option 1, not working as desired
colc=dtt<<NEWCOLUMN></NEWCOLUMN>

你好，

 

我是JSL脚本的新手，而我正在寻求帮助就是编写一个有点挑战性的if和/或for循环。

所以我想循环遍历两个不同值的“代码”和“日期”列，以在名为“ID”的新列中生成介于1-28之间的特定数字。

第一列的重复数值范围为1-7。第二列具有此格式的重复日期，dd / mm / yyyy“dates”。第三列称为输出的“TestID”。

 

我的目标是创建一个新的列“ID”，根据以下匹配生成值1-28。

即/ if codes = 1＆dates = 1st date时，新列中的值为1

然后，当/ if codes = 1＆dates = 2nd date时，新列中的值为2

然后，当/ if codes = 1＆dates = 3rd date ... newcolumn = 3

然后，当/ if codes = 1＆dates =第4个日期... newcolumn = 4

一遍又一遍......

然后，当/ if codes = 2＆dates = 3rd date ... newcolumn = 5

然后，当/ if codes = 2＆dates = 4th date ... newcolumn = 6

。

。

。

直到，

然后，当/ if代码= 7＆dates =第4个日期...列= 28

 

到目前为止，我只能成功编写附加的上述脚本，但它不能按我的意愿工作。

 

这可能有点挑战，但我会提供任何帮助。

 

谢谢！

 

Re：难以在两列上循环以创建第三列

非常感谢Jim。

这非常有效！

 

-IrisK

Re：难以在两列上循环以创建第三列

试试这个公式：

If( Row() == 1,
 Summarize( bygroup = by( :dates ) );
 If( bygroup[1] == "",
  newbygroup = Remove( bygroup, 1 ),
  newbygroup = bygroup
 );
);
(:codes - 1) * 4 + Loc( newbygroup, :dates );

我以前的版本是基于日期的，但它应该是基于代码的。

Re：难以在两列上循环以创建第三列

New Table( "inputdata",
 Add Rows( 308 ),
 New Column( "dates",
  Character,
  Nominal,
  Set Values(
   {"07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "07/10/2017", "07/17/2017", "07/17/2017", "07/17/2017",
   "07/17/2017", "07/17/2017", "07/17/2017", "07/24/2017", "07/24/2017",
   "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017", "07/24/2017",
   "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017", "07/10/2017",
   "07/10/2017", "", ""}
  )
 ),
 New Column( "codes",
  Numeric,
  Continuous,
  Format( "Best", 8 ),
  Set Values(
   [3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3,
   3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,
   4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,
   5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
   6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7,
   7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 2, 2,
   2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
   2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
   1, 1, 1, 1, 1, 1, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4,
   4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 6, 6, 6,
   6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 2,
   2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
   1, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4] 
  )
 ));

是。我将使用下面的if循环粘贴短脚本。并且数据集附在上面。

 

谢谢你们已经看过了！

 

脚本：

德勤 = 数据表 （ “输入数据” ） ;

//选项1，不按预期工作

COLC = 德勤 << NewColumn （ “codefin” ，式 （ 烧焦 （ ： 日期 ） || “_” || 烧焦 （ ： 代码 ））） ;

标识 = 德勤 << 新栏目 （ “ID” ，

数字 ，

连续 ，

格式 （ “最好” ， 8 ） ，

式 （ 如果 （ 行 （） == 1 ，

总结 （ bygroup = 通过 （ ： 日期 ） ） ;

如果 （ bygroup [ 1 ] == “” ，

newbygroup = 去掉 （ bygroup ， 1 ） ，

newbygroup = bygroup

） ;

） ;

（ 禄 （ newbygroup ， ： 日期 ） - 1 ） * 7 + ： 代码 ; ） ，

被选中

） ;

Re：难以在两列上循环以创建第三列

@IrisK

我无法理解你的需求。 您能否提供一个表格，显示日期和代码的值，以及ID的结果值是多少？

Re：难以在两列上循环以创建第三列

你好吉姆，

 

我很抱歉回到这里。我知道我接受了这个答案超过一周agao，但我只是意识到脚本不能正常工作。

 

让我解释：

 

所以你完全明白我想做什么。我需要基本上创建一组ID，这些ID基于代码列和日期列的更改值。

 

目标正是如此：如果4个日期在“日期”列中重复出现，则“代码”列中会重复1-7个数字。我想要“ID”列如下：

Date1 + code = 1> ID = 1

date2 + code = 1> ID = 2

date3 + code = 1> ID = 3

date4 + code = 1> ID = 4

 

date1 + code = 2> ID = 5

date2 + code = 2> ID = 6

date3 + code = 2> ID = 7

。

。

。

 

现在，您帮助我的脚本只允许ID对所有列的date1都正确，因为脚本依赖于代码列中的数字。 所以这样做：

date1 + codes = 1> ID = 1 GOOD

date2 + codes = 1> ID = 8 BAD

date3 + codes = 1> ID = 15 BAD

等等...

 

我真的希望这是有道理的。脚本在正确的道路上！但是，我真的不知道如何根据日期和代码的变化来纠正它。你有想法可以做到吗？  

遗憾的是，我和我的团队必须以格式获取数据。

 

 

Re：难以在两列上循环以创建第三列

谢谢吉姆，

这完美地完成了这项工作！

 

最好，

鸢尾花

Re：难以在两列上循环以创建第三列

我不确定我从您的通信中解释的是否正确，但如果是，则这是一个将生成1-28值的公式，假设“日期”和“代码”的有效值不超过4个有1-7个值。

 

 

If( Row() == 1,
 Summarize( bygroup = by( :dates ) );
 If( bygroup[1] == "",
  newbygroup = Remove( bygroup, 1 ),
  newbygroup = bygroup
 );
);
(Loc( newbygroup, :dates ) - 1) * 7 + :codes;