44083_zh-TW

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;