Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- JSL query: Find a same name row in the same column, then compare the date and fi...

Topic Options

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

JSL query: Find a same name row in the same column, then compare the date and find a later date

Sep 22, 2017 11:29 AM
(1551 views)

Hi All,

I have a datatable below, I want to compare,

1. the SN column first to find if there is a same name row,

2. then compare the same name row to find which is row has later date on column "date1",

3. Then assign that row as "1" to column LLR.

I am using below script, but doesn't work, I don't know how to debug it, can anyone help me to learn?

```
dt = Current Data Table();
New Column( "LLR");
For( i = 1, i <= N Row( dt ), i++,
SNcol = Column( dt, "SN" )[i];
Datecol = Column( dt, "DATE1" )[i];
For( j = 1, j <= N Row( dt ), j++,
If( Column( dt, "SN" )[j] == SNcol,
Column( dt, "DATE1" )[j]>Column( dt, "DATE1" )[i],"1"
"0")));
```

- Tags:
- jsl

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: JSL query: Find a same name row in the same column, then compare the date and find a later date

Here is the table.

SN | DATE1 | LLR | LLR 2 |

a | 7/29/2017 12:27 | 1 | |

b | 9/1/2017 9:24 | 1 | |

b | 9/1/2017 9:25 | 1 | |

c | 9/4/2017 19:46 | 1 | |

c | 8/31/2017 23:22 | 1 | |

c | 9/4/2017 19:27 | 1 |

- Tags:
- jsl

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: JSL query: Find a same name row in the same column, then compare the date and find a later date

I'm not sure that I've understood exactly what you are asking. But if I'm close, the following code might help. If you are starting off with JSL, I would encourage you to use 'Help > Scripting Index' to understand how it works. Also, bear in mind that there are many ways to do this, and this way may or may not be 'better' than the others.

```
NamesDefaultToHere(1);
// Make a table to play with
dt = New Table( "Mark the Latest Date",
Add Rows( 25 ),
New Column( "Group",
Numeric,
"Nominal",
Format( "Best", 12 ),
Formula(RandomInteger(1, 5))
),
New Column( "DateTime",
Numeric,
"Continuous",
Format( "ddMonyyyy h:m:s", 22, 0 ),
Input Format( "ddMonyyyy h:m:s", 0 ),
Formula( Random Uniform( Today() - In Days( 7 ), Today() ) )
),
);
dt << runFormulas;
Column(dt, "Group") << deleteFormula;
dt << Sort(By(:Group), Replace Table);
// Start Here . . .
gVals = Column(dt, "Group") << getValues; // Values in the 'grouping' column
dtVals = Column(dt, "DateTime") << getValues; // Values in the 'DateTime' column
dt << NewColumn("Is Latest?", Numeric, Nominal, ValueLabels({0 = "No", 1 = "Yes"}), Values(J(NRow(dt), 1, 0))); // New column
// Loop over the groups
for(g=1, g<=Max(gVals), g++,
// Find which row the maximum for the current group is in
dtVals2 = dtVals; // Copy all the values
dtVals2[Loc(gVals != g)] = .; // Set values not corresponding to the current group to missing
r = LocMax(dtVals2); // Get the position (row number) of the maximum of the values that are left
// Overwrite the existing "0" value for this row
Column(dt, "Is Latest?")[r] = 1;
);
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: JSL query: Find a same name row in the same column, then compare the date and find a later date

Not sure exactly what you want, but you could try a column formula with the Col Max() function, grouped after SN.

Two examples:

```
dt = Current Data Table();
// Returns 1 for max DATE of each SN
dt << New Column("LLR", Formula(If(Col Max(:DATE1, :SN) == :DATE1, 1)));
// Returns 1 for max DATE of each replicated SN
dt << New Column("LLR2", Formula(If(Col Max(:DATE1, :SN) == :DATE1 & Col Number(:SN, :SN) > 1, 1)));
```