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
bzanos
Level III

How to Get Row Number When There is Missing Data

There is missing value in my data.

I'm trying to create script for new column 'Row Number' to get row number of lagged value of Col1(non-missing data).

I'm facing problem when missing data is encountered.

Below is my data with required row number outcome.

How to create script for this?

 

Row#Col1Row Number(outcome)
111 
2181
3342
4 3
5 3
6 3
7203
8 7
9 7
10247
111410
121711
13 12
14 12
151612
162515

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
XanGregg
Staff

Re: How to Get Row Number When There is Missing Data

I think this will do what you're looking for. Lag() without an argument will give the lag of the current column.

 

New Column( "r",
	Formula(
		If(
			Row() == 1, .,
			Is Missing( Lag( :Col1 ) ), Lag(),
			Row() - 1
		)
	)
)

View solution in original post

2 REPLIES 2
XanGregg
Staff

Re: How to Get Row Number When There is Missing Data

I think this will do what you're looking for. Lag() without an argument will give the lag of the current column.

 

New Column( "r",
	Formula(
		If(
			Row() == 1, .,
			Is Missing( Lag( :Col1 ) ), Lag(),
			Row() - 1
		)
	)
)
bzanos
Level III

Re: How to Get Row Number When There is Missing Data

It works.

Thank you.