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

Question

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

ms · Answer

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

ian_jmp · Answer

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

Stokes · Answer

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