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

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

lwx228 — Fri, 09 Jun 2023 23:29:17 GMT

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.

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

lwx228 — Tue, 16 Jun 2020 09:24:14 GMT

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

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

Thanks!

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

Georg — Tue, 16 Jun 2020 09:36:48 GMT

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

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

lwx228 — Tue, 16 Jun 2020 09:44:05 GMT

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.

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

lwx228 — Tue, 16 Jun 2020 09:45:52 GMT

It's equivalent to VBA

Cells(Rows.Count, 4).End(3).Row

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

lwx228 — Tue, 16 Jun 2020 10:10:13 GMT

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

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

Georg — Tue, 16 Jun 2020 10:29:48 GMT

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

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

lwx228 — Tue, 16 Jun 2020 12:50:43 GMT

Thank Georg!

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