formula

[#1]

Hello everyone,

I have a simple question for you, I would like to build the following query using formulas in JMP if possible:

Put in the "error" column the value 1 one line after a cell in the "balance" column is non-zero.

Here is the database:

Line 38,061 should have the value in the "balance sheet" column be 1 and line 38,060 not.

Thank you for your answers !

[#2 · Accepted solution]

Using Lag() (and possibly check for first row) should be enough here.

Names Default To Here(1);

dt = New Table("Untitled 5",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("Column 1", Character, "Nominal", Set Values({"", "aaa", ""}))
);

dt << New Column("Col", Numeric, Nominal, Formula(
	Row() != 1 & !IsMissing(Lag(:Column 1, 1))
));

[#3 · Accepted solution]

Jarmo's formula works fine, but it can be simplified to

If( Is Missing( Lag( :Bilan ) ) == 0,
	1,
	0
)

which will increase the runtime efficiency