629980_en-US

liliconcom

Create Column with Values Based Off Existence of Other Rows & Logic

Hello anyone, 

I am trying to create a formula or script that will set a certain value ("Good", "Bad", or "N/A") for each row based off if there is another row in the table with values matching logic that uses the current row as an input.

 

I have a script that does just this by using nested For loops when using a small subset of the full datatable. However, with the full datatable I am trying to use which is roughly 1 million rows, the script just crashes JMP.

Is there a more efficient way in order to allow JMP to perform this? (Or if it can be done with SQL, I can do that before bringing it into JMP).

 

See attached for the script that does what I am talking about.

Data Blending and CleanupRe: Create Column with Values Based Off Existence of Other Rows & Logic

Usually there is no need to nest for loops when working with data tables.

 

Could you provide small subset of your data and include a column with the results you want in it? 

Re: Create Column with Values Based Off Existence of Other Rows & Logic

Okay, I will mock up the data though because of privacy reasons.

 

colA	colB	colC	colDate	colE	Logic
A0001	16	100	2022/04/01 9:15:01 AM	Action1	N/A
A0001	100	182	2022/04/01 9:15:31 AM	Action2	Good
A0001	100	100	2022/04/01 9:15:36 AM	Action1	N/A
A0002	212	200	2022/04/02 3:15:21 AM	Action1	N/A
A0002	211	282	2022/04/02 3:15:11 AM	Action2	Bad
A0002	212	200	2022/04/02 3:15:16 AM	Action1	N/A
A0003	441	382	2022/05/01 1:15:31 AM	Action2	Bad
A0004	441	382	2022/05/01 5:16:31 AM	Action2	Bad
A0004	441	300	2022/05/01 5:15:31 AM	Action1	N/A

 

 

If the logic of the original script wasn't clear:

If current row 's colE is "Action 1": then assign "N/A"; 
Else, if current row's colE is "Action 2", then then follow the logic in the next paragraph.

 

If there exists another row in the table with the same values of the current row for columns colA and colB; current row's colC value is exactly 82 more than this other row; colDate values are within 18 seconds of each other; current row's colE is "Action 2" and this other row's colE is "Action 1": then assign "Good"; else then assign "Bad".

 

 

Appreciate any help! I'm just not seeing a way without nested loops at the moment.

But I do now at least realize I should put a Break() after Line 25 of my original script. No reason to keep looking for another row that matches my criteria if I already found at least one exists.

Re: Create Column with Values Based Off Existence of Other Rows & Logic

Logic in this script is overly complicated as I don't know all the limitations of the data (can there for example be multiple matches) and I didn't optimize it

Names Default To Here(1);

dt = New Table("Untitled",
 Add Rows(9),
 Compress File When Saved(1),
 New Column("colA", Character, "Nominal", Set Values({"A0001", "A0001", "A0001", "A0002", "A0002", "A0002", "A0003", "A0004", "A0004"})),
 New Column("colB", Numeric, "Continuous", Format("Best", 9), Set Values([16, 100, 100, 212, 211, 212, 441, 441, 441])),
 New Column("colC", Numeric, "Continuous", Format("Best", 9), Set Values([100, 182, 100, 200, 282, 200, 382, 382, 300])),
 New Column("colDate",
  Character(22),
  "Nominal",
  Set Values(
   {" 2022/04/01 9:15:01 AM", " 2022/04/01 9:15:31 AM", " 2022/04/01 9:15:36 AM", " 2022/04/02 3:15:21 AM", " 2022/04/02 3:15:11 AM",
   " 2022/04/02 3:15:16 AM", " 2022/05/01 1:15:31 AM", " 2022/05/01 5:16:31 AM", " 2022/05/01 5:15:31 AM"}
  )
 ),
 New Column("colE",
  Character(16),
  "Nominal",
  Set Values({"Action1", "Action2", "Action1", "Action1", "Action2", "Action1", "Action2", "Action2", "Action1"})
 ),
 New Column(" Logic", Character(16), "Nominal", Set Values({" N/A", " Good", " N/A", " N/A", " Bad", " N/A", " Bad", " Bad", " N/A"}))
);

dt << New Column("Group", Character, Nominal, << Set Each Value(char(:colA)||char(:colB)));
//fix date
Column(dt, "colDate") << Set Each Value(Substitute(:colDate, " AM", "")) << Data Type(Numeric);

dt << New Column("Logic", Character, Nominal, << Set Each Value(
 As Constant(
  m_group = :Group << get values;
  m_c = :colC << get values;
  m_date = :colDate << get values;
 );
 
 If(:colE == "Action1", 
  "N/A";
 , :colE == "Action2",
  cur_group_idx = Loc(m_group, :Group);
  If(N Items(cur_group_idx) == 0,
   "Bad"
  ,
   plus_idx = cur_group_idx[Loc(:colC - 82 == m_c[cur_group_idx])];
   If(N Items(plus_idx) == 0,
    "Bad"
   ,
    timediff_idx = Loc(Abs(m_date[plus_idx] - :colDate) <= 18);
    If(N Items(timediff_idx) > 0,
     If(:colE == "Action2" & :colE[timediff_idx[1]] == "Action1",
      "Good"
     ,
      "Bad"
     )
    ,
     "Bad"
    );
   )
  );
 , 
  "Bad";
 );
));

dt << Delete Column("Group");