374921_en-US

liliconcom

find first element with condition in a column

Hi, I have a data with 2 columns: group and score, and I want to calculate the 3 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	.

AnalyticsRe: find first element with condition in a column

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

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

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

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

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=""></NEW>