Support needed: How to fill variable number of cells in a data table

[#1]

Hello JMP Community,

Attached is a sample data table and the target table that I am aiming for.

The data table is organized with a parent row and a variable number of child rows under the parent row.  The parent row also has a column which indicates the Status of the account (see last column of Sample.jmp).  I would like to copy the status of the parent row to the child rows (see last column of TargetFile.jmp).  I believe that Lag() is the correct function, I just can't figure out how to deal with variable number of child rows?

Many thanks in advance!

Sincerely,

MG

[#2 · Accepted solution]

You can certainly do this with Lag() but you might also be able to do this interactively in JMP (not sure which version gives this option, I'm using JMP16.1... there are also addins to do this).

Select the column you want to fill

Right click on top of the values and select Fill / Replace Missing with Previous value

Edit:

With Lag one way to do this is to create new column and use formula like:

If(Row() == 1, val = :Status);
If(:Status == "",
	val,
	val = :Status
);

[#3]

Thanks @jthi