find first element with condition in a column

Question

Hi, I have a data with 2 columns: group and score, and I want to calculate the 3rd column called first, which returns the "first score by group that is not 10000000000. so in this sample data, group a = 0, group b =53. and since group c only contains 10000000000, nothing shows up for it.

Could someone help me with a jmp formula for this column but not jsl? Thank you so much!

groupscorefirsta100000000000a00a1000a100000000000a80a100000000000a100000000000a100000000000b5353b1000000000053b153c10000000000.c10000000000.c10000000000.

txnelson · Answer

Here is a different approach to solving the problem.  This approach is a script.  It should be more efficient that the previous single formula column approach.  It first finds the first values, extracts them to a new table, and then uses the Update platform to merge the values back into the original data table.

names default to here(1);
dt=current data table();

// Create a column that finds the first values
dt<<new column("theSelected",formula(If( :group != Lag( :group ),
	flag = 0
);
If( :score != 10000000000 & flag == 0,
	flag = 1;
	value = 1;
,
	value = .
);
value;));

// Select all of the rows that are marked as first values
dt<<select where(:theSelected==1);

// Create a subset of the marked rows
dtSel=dt<<subset(selected rows(1),columns({group,score}));

// Delete the marking column in the original data table
dt<<delete columns(theSelected);

// Change the name or the score column in the new data table 
// because when merged back into the original table it needs
// a different name
dtSel:Score << set name("First Value");

// Merge the first found data back into the original table
dt << Update(
	With( dtSel ),
	Match Columns( :group = :group )
);

// Delete the first selected data table
close(dtSel,nosave);

// Remove row selection from the original table
dt << clear select;

ms · Answer

That's a lot of rows. This formula may require less memory.  It assumes the first value ≠ 10000000000 always is less than 10000000000.

If(Col Min(:score, :group) == 10000000000,
    .,
    Col Min(
        :score[Col Min(Row(), :group, :score == 10000000000)],
        :group
    )
)

joann · Answer

Thank you Jim! I pasted your formula directly to my data and checked again- it worked on top ~7000 rows, but from that on it's all ".", not showing the first number that's not 10000000. When I entered the formula it did run for long time so I had to pressed esc to unfreeze jmp. is that causing this to happen? I totally have around 7000k rows.