topic find first element with condition in a column in Discussions

find first element with condition in a column

joann — Fri, 09 Jun 2023 00:31:47 GMT

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!

group	score	first
a	10000000000	0
a	0	0
a	100	0
a	10000000000	0
a	8	0
a	10000000000	0
a	10000000000	0
a	10000000000	0
b	53	53
b	10000000000	53
b	1	53
c	10000000000	.
c	10000000000	.
c	10000000000	.

Re: find first element with condition in a column

txnelson — Wed, 07 Apr 2021 17:39:41 GMT

Here is one way to write the formula

curgroup = :group;
value = :score[Min(
	Current Data Table() << get rows where(
		:group == curgroup & :score != 10000000000
	)
)];

Re: find first element with condition in a column

joann — Wed, 07 Apr 2021 20:22:55 GMT

Hi Jim, thank you for the answer. I pasted it into the formula and replace the column name, but it didn't produce the right answer. Could you help me check the below screenshot? Thank you.

Re: find first element with condition in a column

txnelson — Wed, 07 Apr 2021 23:32:34 GMT

The screen dump of the formula does not let me see if you are referencing the columns correctly. That is, are you placing a ":" in front of the column names, but not in front of curgroup and value?

What error messages are you getting?

When you say that it is not working correctly, what does that mean? Not getting the correct values, or is it not giving you any results?

I have attached your sample data table with a column called Jim's Formula that has my formula applied to the data table. This is so you can have a working version of the formula for comparison to what you are attempting to create.

Re: find first element with condition in a column

joann — Thu, 08 Apr 2021 14:34:11 GMT

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.

Re: find first element with condition in a column

ms — Thu, 08 Apr 2021 15:14:37 GMT

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

Re: find first element with condition in a column

txnelson — Thu, 08 Apr 2021 16:36:24 GMT

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;