Subsequent rows dependent on different columns from previous rows

[#1]

Hello - I'm working with a set of experiments where some experiments are dependent on previous experiments (i.e. I'm mixing solutions I've previously made to make new ones). I'm trying to make a function where it reads a column to get the name of the previous solution, searches a second column to find the row with that name, then takes the value in a cell with that row and a third column. Match seems to kindof do this, but I can't get it to work, and I think its in part because match expects an explicitly stated level of the search column, and maybe other stuff I'm not realizing. I'm not at all experienced in JSL and would prefer something like a match column function, but if JSL is what this takes, then that's totally fine.

I've attached a dummy table that should get the point across without seeing all of my extraneous other columns. The last column is the one with the formula that's failing, and here's a screenshot

and the code for what isn't working if that's easier to deal with:

Match( :Solution name, :Used solution for dilution, :Given Concentration ) /
:Dilution Factor

Thanks in advance.

[#2 · Accepted solution]

One option is to use something like this to get the index

row_idx = Loc(:Letters << Get Values, :Used solution for dilution);
If(N Items(row_idx) < 1,
	.,
	row_idx[1]
);

then you can use Data table subscripting to access value on the row specific row. So the final formula is something like this

row_idx = Loc(:Letters << Get Values, :Used solution for dilution);
If(N Items(row_idx) < 1,
	.,
	:Given Concentration[row_idx[1]] / :Dilution Factor
);

[#3]

Hey @jthi - This worked! Just so I understand the logic, since this is my first time engaging with JSL, and I'd rather not completely infer what's going on:

1. the :Column1 << Get Values creates a temporary list that's identical in content and order to :Column1.

2. Loc(:Column2 << Get Values, :Column2) then gives an ordered list of all the row numbers where :Column1 matches the contents of each row in :Column2. We assign that to row_idx.

Within the if statement,

3. N Items( row_idx ) < 1 says that if the list is null (i.e. there are no matches), we make it blank by putting a "." in,

4. If there are greater than or equal to 1 entry in the row_idx list, :Column3[row_idx[1]] is an index of an index, where we take the first entry in the row_idx list, use that entry as the nth entry of :Column3.

Is that the gist of it?

Thanks for working on this!

[#4]

<< Get Values is used to get the values from column into a list or matrix (depends on data type).

Loc() can be used to get the indices from the matrix/list which match the item we provide it with OR return indices of non-missing values (very good function to know in my opinion).

Loc will return a matrix, so we use N Items() to check if there are any indices found to avoid issues when accessing the indices. If no index is found we set value for those rows as missing ".".

Based on the data provided, I chose to always use first index from the indices. It is possible to use all of them if more complicated calculations were needed in case of multiple matches.

Below is the formula creation with some comments

Names Default To Here(1);

dt = New Table("Calculated concentration - Dummy table",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("Given Concentration", Numeric, "Continuous", Format("Best", 12), Set Property("Units", "ppm"), Set Values([12, 50, 23, 14, 2, .])),
	New Column("Letters", Character(1), "Nominal", Set Values({"A", "B", "C", "D", "E", "F"}), Set Display Width(38)),
	New Column("Used solution for dilution", Character, "Nominal", Set Values({"", "", "", "", "", "C"})),
	New Column("Dilution Factor", Numeric, "Continuous", Format("Best", 12), Set Values([., ., ., ., ., 2.29837984]))
);

dt << New Column("Calculated Concentration (ppm)", Numeric, Continuous, Formula(
	// use As Constant to evaluate this only once (should make it more efficient if you have lots of data)
	As Constant(all_letters = :Letters << get values); // << get values gets values from column into a matrix or list
	idx_for_letter = Loc(all_letters, :Used solution for dilution); // Loc can be used to return indices of matches in matrix but it has many other uses
	If(N Items(idx_for_letter) == 0, // to avoid issues when trying to access incorrect indices, check for empty matrix
		. // set missing value to column IF no indices found
	,
		:Given Concentration[idx_for_letter[1]] / :Dilution Factor // perform calculation using first index found from idx_for_letter
	);
));

To get explanation about used functions I would suggest first using Scripting Index to search for the function and then clicking "Topic Help" to get to help page. For example search for "as constant"

and click topic help to open this page As Constant(expr) 

[#5 · Accepted solution]

Here is the formula that I have come up with.  It may be able to be simplified, but in it's current form it appears to work.

x = .;
If( :Used solution for dilution[Row()] != "",
	curRow = Row();
	targetRow = (Current Data Table() << get rows where(
		:Letters == :Used solution for dilution[curRow]
	))[1];
	x = :Given Concentration[targetRow] / :Dilution Factor[curRow];
);
x;

[#6]

@txnelson Thanks for the code; your's worked too! I don't have any experience with JSL, any only have worked with VBA and Matlab, so like with @jthi, I want to understand. So to make sure I understand

1. x=. just defines an empty variable x.

2. If there are cells from :Used solution for dilution in the same row as the one being evaluated that are not blank =>

3. curRow = Row() defines curRow as the current row number

4. get rows where(
:Solution name == :Used solution for dilution[curRow]
))[1] 

grabs the value of the :Used solutions for dilution for the current row by using that as an index, and "get[s] row[ numbers] where" those values are identical to those in :Solution.

5. targetRow = (Current Data Table() << get rows where(... then makes a matrix from the rows determined in step 3, the subscript 1 grabs the first entry in this matrix, .

6. Finally x is filled by dividing the row of :Given Concentration that we specified in 4, by the :Dilution Factor in the current row, specified in 3.

7. We return "x" to the cell.

My one question is from step 5, I would think targetRow is a list of each entry in the Row we want, but then for step six to work it has to return the actual row number. I think I'm misunderstanding how the Current Data Table  () << get rows where(... works. Could you elaborate?

Thanks again for taking the time! 

[#7]

<< Get Rows Where() returns a matrix of the rows that match the selection criteria passed to the function.

So, when the a row is found to have a value for the column "Used Solution for Dilution" is not blank/missing, 

1. The memory variable, curRow, is set to the current row, Row(), that is being processed.  In this case, curRow will be set to 6.
2. targetRow, needs to be set to the row that contains the "Given Concentration" from the row that has a matching value in the column "Letters", with the current row value for "Used Solution for Dilution".
   1. When parsed, the Get Rows Where is:

      << get rows where(:Letters == :"C"])

      The function allows for multiple rows to be returned, therefore, it returns a matrix of all of the row numbers that match the selection criteria.  In our case, a single element matrix will be returned, and we want the value of the first element,  which is 3, the row number for the row, where column Letters is equal to "C"