cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
lwx228
Level VIII

How do can find the number of non-null rows in the specified column?

 

 

For example:

dt = Open( "$SAMPLE_DATA/Data\Missing Data Pattern.jmp" );


the number of non-null rows in the "Trial 3" column of this table is 5,
and the number of non-null rows in the "Trial 4" column is 4.

2020-06-16_17-02.png

1 ACCEPTED SOLUTION

Accepted Solutions
Georg
Level VII

Re: How do can find the number of non-null rows in the specified column?

probably this script would do it?

Names default to here(1);

dt = Open( "$SAMPLE_DATA\Missing Data Pattern.jmp" );

x=column(dt, "Trial 4") << get values();
// y =sum(!is missing(x));
// show(x, y);

y = !is missing(x);
rows = transpose(1::n items(y));
show(max(y :*  rows));
Georg

View solution in original post

7 REPLIES 7
lwx228
Level VIII

Re: How do can find the number of non-null rows in the specified column?

I don't know how to express it in this formula, right?

 

Contains(:[Index(1,N Row(dt))],,-1)

Thanks!

Georg
Level VII

Re: How do can find the number of non-null rows in the specified column?

There are many ways, one May be this one:

 

Names default to here(1);
dt = Open( "$SAMPLE_DATA\Missing Data Pattern.jmp" );

x=column(dt, "Trial 4") << get values();
y =sum(!is missing(x));
show(x, y);
Georg
lwx228
Level VIII

Re: How do can find the number of non-null rows in the specified column?

Thanks!
I'm not being precise here.


It should be:
The row number of the last non-null value in the "Trial 3" column is 5,
The row number of the last non-null value in the "Trial 4" column is 4.

lwx228
Level VIII

Re: How do can find the number of non-null rows in the specified column?

It's equivalent to VBA

Cells(Rows.Count, 4).End(3).Row
lwx228
Level VIII

Re: How do can find the number of non-null rows in the specified column?

I just don't know how to change this particular value into a generic variable form.

2020-06-16_18-07.png

Georg
Level VII

Re: How do can find the number of non-null rows in the specified column?

probably this script would do it?

Names default to here(1);

dt = Open( "$SAMPLE_DATA\Missing Data Pattern.jmp" );

x=column(dt, "Trial 4") << get values();
// y =sum(!is missing(x));
// show(x, y);

y = !is missing(x);
rows = transpose(1::n items(y));
show(max(y :*  rows));
Georg
lwx228
Level VIII

Re: How do can find the number of non-null rows in the specified column?

Thank Georg!

s4 = Max(
    !Is Missing( Column( 4 ) << get values() ) :* Transpose(
        1 :: N Items( !Is Missing( Column( 4 ) << get values() ) )
    )
);