nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Add data from previous columns if missing

JulieSAppel

Hi,

I´m trying to create a column with the latest available date in my data set. So in this case, if a date is missing form visit 19, I get the the date from visit 18. That I can do. But if the visit 18 date is missing I can´t make it work anymore. So how do I make jmp start in one column and go back until there is a date?

Br Julie

test dates.jmp

Accepted answers

txnelson

Here are 2 ways of doing this

Names Default To Here( 1 );
dt = Current Data Table();

For( i = 1, i <= N Cols( dt ), i++,
	missingRows = dt << get rows where( Is Missing( As Column( i ) ) );
	If( N Rows( missingRows ) > 0,
		For( k = 1, k <= N Rows( missingRows ), k++,
			Column( i )[missingRows[k]] = Column( i - 1 )[missingRows[k]]
		)
	);
);

Names Default To Here( 1 );
dt = Current Data Table();

For( i = 1, i <= N Rows( dt ), i++,
	If( Is Missing( As Column( 19 )[i] ),
		Column( 19 )[i] = Max( dt[i, 0] )
	)
);

All comments

Mark_Bailey

If you start iterating from the first row, you should be able to fill in a missing date with the previous date as you iterated down the table. It seems simple enough to me but I might have missed an important point.

txnelson

Here are 2 ways of doing this

Names Default To Here( 1 );
dt = Current Data Table();

For( i = 1, i <= N Cols( dt ), i++,
	missingRows = dt << get rows where( Is Missing( As Column( i ) ) );
	If( N Rows( missingRows ) > 0,
		For( k = 1, k <= N Rows( missingRows ), k++,
			Column( i )[missingRows[k]] = Column( i - 1 )[missingRows[k]]
		)
	);
);

Names Default To Here( 1 );
dt = Current Data Table();

For( i = 1, i <= N Rows( dt ), i++,
	If( Is Missing( As Column( 19 )[i] ),
		Column( 19 )[i] = Max( dt[i, 0] )
	)
);

JulieSAppel

But won´t that just fill in the last column with the latest date instead of creating a new column?

Also, in the first ecample, why is it "i = 5"?

Thanks in advance

txnelson

My error on the i=5. I was testing with it....I have changed it to i=1.

If you want a new column, then you can just add it as your 20th column, and use the second script.

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column("Last Date Seen",format("yyyy-mm-dd",12));

For( i = 1, i <= N Rows( dt ), i++,
	If( Is Missing( As Column( "Last Date Seen" )[i] ),
		Column( "Last Date Seen" )[i] = Max( dt[i, 0] )
	)
);