topic Re: Recoding Columns using IF statements in Discussions

Recoding Columns using IF statements

lizrdee — Fri, 27 Sep 2019 16:28:43 GMT

Hello JMP Experts,

I am new to IF statements and would like to use one (or something else that would accomplish the same thing) to recode a column that has a value tied to another columns value.

So in the example below:

EndTime Id is a unique run ID for 8 channels.

There are 10 repair intervals.

I would like to assign a continuous run number (see empty column) for each unique EndTime Id, that restarts with 1 when the repair interval changes.

Thank you!

Re: Recoding Columns using IF statements

Thierry_S — Fri, 27 Sep 2019 18:14:49 GMT

Hi,

You could use the following formula (copy and paste in the Formula Editor)

	If( Row() == 1,
		1,
		If( :EndTime Id != Lag( :EndTime Id, 1 ),
			Lag( :RUN NUMBER, 1 ) + 1,
			Lag( RUN NUMBER, 1 )
		)
	);

This will only work properly if the table is sorted by EndTime Id

Re: Recoding Columns using IF statements

Thierry_S — Fri, 27 Sep 2019 19:17:54 GMT

There is a small error in the formula: I forgot to precede one to RUN NUMBER call with ":"

If( Row() == 1,
	1,
	If( :EndTime Id != Lag( :EndTime Id, 1 ),
		Lag( :RUN NUMBER, 1 ) + 1,
		Lag( :RUN NUMBER, 1 )
	)
);

Re: Recoding Columns using IF statements

ron_horne — Sun, 29 Sep 2019 08:59:00 GMT

Hi @lizrdee

Most likely @Thierry_S 's answer is sufficient.

Otherwise, take a look at this discussion
How do I create an index column by ID?