Calculate Column Difference for other column levels where lag varies

ashwint27

I have a column :EVENT (character-nominal) with various values.  Also another column :datetime (numeric-continuous).  I have :datetime column sorted ascending.  I am interested in having another column calculate for whenever there is a "CONNECT" event, what is the time difference between it and the last "DISCONNECT" event.  The issue is that the lag can vary between a CONNECT and previous DISCONNECT (see photo below) and I'd also prefer keeping this tall table format.  A solution for datetime sorted-ascending is appreciated, but also not sorted would also be beneficial.

 

jthi

You can for example use variable in the formula to store the value of last disconnect

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(12),
	Compress File When Saved(1),
	New Column("E", Character, "Nominal", Set Values({"A", "B", "Disc", "Con", "A", "B", "C", "B", "Disc", "A", "B", "Con"})),
	New Column("T", Numeric, "Continuous", Format("Best", 12), Set Values([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]))
);

dt << New Column("Dif", Numeric, Continuous, Formula(
	If(Row() == 1,
		last_disc = .;
		retval = .;
	);
	If(:E == "Disc",
		last_disc = :T;
		retval = .;
	, :E == "Con",
		retval = :T - last_disc
	,
		retval = .;
	);
	retval;
));