topic Re: 53 Time columns, some empty and some filled. How do I write a formula ensuring the highest filled column is > than next filled column etc. in Discussions

53 Time columns, some empty and some filled. How do I write a formula ensuring the highest filled column is > than next filled column etc.

fionaweston — Sat, 10 Jun 2023 23:37:38 GMT

If I have hundreds of rows, each row is a separate work ticket, each row can have up to 53 time columns filled in, but most only have some of these columns filled in and ones in between are blank.

So say for Row #1: I have column 1, column 23 and column 36 are the only columns containing a time stamp, I will need know that 36>23 and 23>1.

For Row #2 all columns have time stamps so I will need to know that [53>52,............,2>1].

Thank you

Fiona

Re: 53 Time columns, some empty and some filled. How do I write a formula ensuring the highest filled column is > than next filled column etc.

fionaweston — Mon, 27 Sep 2021 20:25:57 GMT

Just to clarify; They should all be correct so I'm thinking the easiest way is to have a flagging column that will display a "1" at end of any row where above is not the case.

Re: 53 Time columns, some empty and some filled. How do I write a formula ensuring the highest filled column is > than next filled column etc.

txnelson — Mon, 27 Sep 2021 22:10:36 GMT

I am not exactly sure that this is what you want, but the script below scans across the columns and makes sure that all date values are increasing from the previous date. If not, it places the name of the column that has detected an earlier date than the previous date.

Names Default To Here( 1 );
dt = Current Data Table();
If( Try( dt:flag << get name, "" ) == "",
	dt << New Column( "Flag", Character )
);
For( rowNum = 1, rowNum <= N Rows( dt ), rowNum++,
	For( startCol = 1, startCol <= N Cols( dt ), startCol++,
		If( Is Missing( Column( startCol )[rowNum] ) == 0,
			holdVal = Column( startCol )[rowNum];
			Break();
		)
	);
	For( col = startCol + 1, col <= N Cols( dt ) - 1, col++,
		If( Is Missing( Column( Col )[rowNum] ) == 0,
			If( Column( Col )[rowNum] < holdVal,
				dt:Flag[rowNum] = (Column( Col )) << get name;
				holdVal = Column( col )[rowNum];
				
				Break();
			,
				holdVal = Column( Col )[rowNum]
			)
		)
	);
);

Re: 53 Time columns, some empty and some filled. How do I write a formula ensuring the highest filled column is > than next filled column etc.

fionaweston — Mon, 27 Sep 2021 23:53:39 GMT

This is great, even better than what I was asking for because it tells me exactly where the condition is untrue.

Thanks!

Re: 53 Time columns, some empty and some filled. How do I write a formula ensuring the highest filled column is > than next filled column etc.

jthi — Tue, 28 Sep 2021 08:56:48 GMT

Here is one possible solution using Matrices. If no information about column name is needed, it can be made shorter by removing result_column and calculations related to it.

Names Default To Here(1);

dt = Current Data Table();
result_flag = [];
result_column = {};
For Each Row(dt,
	m_all = dt[Row(), 0]; //get all values of row to matrix
	m_values = m_all[Loc(m_all)]; //remove missing values;
	start = Insert(m_values[1::N Items(m_values)-1], 0, 1); //shift right by 0 and remove last value
	end = m_values;
	//if we only want to know if any value is smaller than previous, we can just us any
	Insert Into(result_flag, Any(end - start < 0)); //we could stop here if we don't want to know the column name
	//but that doesn't tell us which column(s) breaks the "rule"
	idx = Loc(end - start < 0); //indices of value matrix which break the rule -> will require checking for empty
	If(N Items(idx) == 0,
		Insert Into(result_column, "");
	, //else
		breaking_col = Min(Loc(m_all, m_values[Min(idx)])); //values which break rule, we can most likely just get the first index?
		Insert Into(result_column, Column(dt, breaking_col) << get name);
	);
);

dt << New Column("Flag", Numeric, Nominal, Values(result_flag));
dt << New Column("Column_Flag", Character, Nominal, Values(result_column));

Could most likely be also used as a formula, but wouldn't most likely recommend as it is fairly easy to break:

dt << New Column("Formula_Flag", Numeric, Nominal, Formula(
	dt = Current Data Table();
	m_all = Matrix(dt[Row(), 0]);
	m_values = m_all[Loc(m_all)];
	start = Insert(m_values[Index(1, N Items(m_values) - 1)], 0, 1);
	end = m_values;
	Any(end - start < 0);
));

Re: 53 Time columns, some empty and some filled. How do I write a formula ensuring the highest filled column is > than next filled column etc.

fionaweston — Tue, 28 Sep 2021 22:35:29 GMT

Jarmo,

This works very nicely too.

I have not done much with Matrices but looks interesting. I will have to go thru it and make sure I understand it.

Thanks