Addressing cells programmatically
I fear I am missing something obvious here – so apologies if this is a stupid question…
I can explicitly reference a subset of cells within a matrix as e.g. |Matrix1::A1:B1| – but how do I achieve the same with script variables?
|Matrix1|.categories[0].items[0].values=1 achieves the same as
|Matrix1::A1|.values =1
but how do I achieve the equivalent of
|Matrix1::A1:B1|.values =1
A separate (but I suspect related) question/issue – I can’t find any way to iterate over a set of cells, for example to check which have a certain value, and then find the Item values for those cells. (I have a workaround for this by creating a “shadow” matrix with calculated items containing the @id values, building parallel arrays of the values and the ids, checking the value list and then indexing into the id list – but that seems very convoluted!
And under all this I wonder if the issue is that the scripting engine doesn’t expose a “cell” object – which is what I would want to iterate over.
I thought the cellsOnly attribute on an Item might be the answer (“Gets the cells for this item, excluding the item itself.”), but that seems to return just the Item name. How is that supposed to be used?
Thanks
Hi Ben,
Many thanks for coming up with this topic again. Unfortunately, I don’t have the time now to explain why I need this functionality but I can describe my reasons next week.
The very short version:
It is mainly for implementing Linear Algebra in Quantrix. I don’t have the need to get the coordinates of a cell in multi-dim matrix (multi-dim: > 2). But I am sure this will be useful for other use cases.
Dominik
Ben, I’d be happy to provide more input if I can, but I’m not sure quite how I can be more specific than in the previous posts.
I found a workaround for the issue of iterating over cells in a matrix of arbitrary dimensionality, as I described. That enables one to find the category/item values of any cell satisfying a value test, but it lies well beyond what a typical non-programming type could be expected to do. In my case the need arose to be able to address/test cells in a 6 dimensional matrix – whereas the current API only allows one to access cells in a 3 dimensional matrix directly (with e.g. |Matrix1|.categories[1].items[3].values.get(1) )
We don’t have any means to directly set or check other properties of individual cells e.g. “is calculated”, formats, conditional input, etc.
I think that adding to the API would be natural, in principle. First add a “cell” object to the object model (in the same way as you already have Category and Item objects). One could then address cells by e.g. listOfCellObjects = |Matrix1|.cells or |Matrix1|.categories[n].cells.get() (etc). You would then provide various methods on the cell object e.g. to test the isCalculated flag, to set a format, conditional input, etc. You’d also need cellRef.getCategories() etc to be able to get from the cell object back to it’s owning categories/items
More than that would probably benefit from direct discussion. Mike has my contact details if you’d like to do that.
Regards
Simon
Hi Simon and Dominik,
We have been revisiting the topic of cell functionality in scripting lately and I wanted to poll the two of you about whether you still have strong use cases for this feature. In particular, you had previously requested the ability to programmatically determine whether a cell was calculated or could have its value set, and also to retrieve the item coordinate that locates a particular value when iterating over the values in a range. Are these still items of interest? If so, can you provide concrete use cases in order to help us understand how these features would best be integrated into the API? We would also be interested in any new functionality along these lines for which you have noticed a need. The more you can provide specific examples of how you would use these things, the better.
Thanks,
Ben
Thanks Ben. And in the meantime, the code below and attached example provides a workaround – albeit a little cumbersome.
You can create a copy of the matrix of interest and add a dimension with the ids calculated for each cell, and then iterate over the cells to find the values of interest.
[code:15rd6l7c]
{
def myShadowMatrix = |Shadow|.matrix
def valueslList = myShadowMatrix.columnTrayCategories[0].items[0].values // linked to original matrix
def keyList = [][] // to store category ids
def targets = [] // to record cells meeting criteria
// set up lists for category ids – allows for any number of categories in the base matrix,
//provided the shadow is arranged as shown
def catCount = myShadowMatrix.columnTrayCategories[0].items.size – 1
for (def i=0; i< catCount; i++) keyList[i] = myShadowMatrix.columnTrayCategories[0].items[i+1].values
valueslList.eachWithIndex {it, i ->
if ( it>20 ) {
// ^^^ this is the test to be applied to the cells
def target = []
for (def j=0; j<catCount; j++) target << keyList[j][i]
targets << target
}
}
}
[/code:15rd6l7c]
Hi Simon,
I think this is indeed a limitation in the current API. It seems this situation may call for something like a “Cell” type. A range or selection could then return a corresponding list of cells, where a cell would have properties to indicate (for example) the item(s) to which it belongs. Such a type would also be a good place for the “isCalculated” and “canSetValue” properties Dom mentioned in another thread. I brought this up to the powers that be and it’s a possible addition for 4.1.
Ben
Thanks very much Dominik. It seems I had got to about the same place as that. But the issue is next – assume I want only to replace the 4’s in Item Foo. I really want to be able to iterate over the cell [b:2aaqn64e]objects [/b:2aaqn64e]and check their item values, rather than iterate over the cell values.
I can achieve the required result by constructing another array containing the IDs for the cells and using that to do the check – but it seems very messy.
I’ll post the code to do that a little later just so you can see more exactly what I mean.
At the more basic level you note:
[code:2aaqn64e]
// Getting the value of a single cell
// All three statements deliver the same result
alert(|Matrix1::B4:A2|.value)
alert(|Matrix1::B|.items[3].values.get(1))
alert(|Matrix1|.categories[1].items[3].values.get(1))
[/code:2aaqn64e]
but that only works for a 2 dimensional matrix – I don’t see how you could reference a single cell in a three dimensional matrix with that method?
Simon,
I try to give you an answer to your questions here based on what I have understood so far. Your questions are exactly the ones I had. Here is a summary of my findings and the information provided by Ben (Many thanks, Ben!):
[b:2472y52s]Getting the value of a single cell[/b:2472y52s]
[code:2472y52s]
// Getting the value of a single cell
// All three statements deliver the same result
alert(|Matrix1::B4:A2|.value)
alert(|Matrix1::B|.items[3].values.get(1))
alert(|Matrix1|.categories[1].items[3].values.get(1))
[/code:2472y52s]
[b:2472y52s]Change a particular value in Matrix1[/b:2472y52s]
We are going now to replace the 4’s in Matrix1 with a 9:
You can replace the hard coded numbers in the brackets in the examples above by index variables for looping through a matrix:
[code:2472y52s]
// ————————————
// Count the appearance of 4 in Matrix1
// ————————————
def int i // column index
def int j // row index
def int countColumns = |Matrix1|.categories[0].items.size()
def int countRows = |Matrix1|.categories[1].items.size()
def int countFoursInMatrix = 0
for(i = 0; i < countColumns; i++)
{
for (j = 0; j < countRows; j++)
if(|Matrix1|.categories[1].items[j].values.get(i) == 4)
{
countFoursInMatrix++
}
}
alert(“”Appearances of 4: “” + countFoursInMatrix + “” times””)
[/code:2472y52s]
[b:2472y52s]Replace a particular value in Matrix1[/b:2472y52s]
We are going now to replace all 4s in Matrix1 with 9s.
[code:2472y52s]
// ————————————
// Replace all 4s in Matrix1 with 9s
// ————————————
def int i
// Get the size (number of values) of Matrix1
def int matrixSize = |Matrix1|.values.size()
// Assign all values of Matrix1 to an ArrayList
def ArrayList<Item> vals = |Matrix1|.values
def int countNinesInMatrix = 0
for(i = 0; i < matrixSize; i++)
{
// Check if the value at index i is equal to 4
if(|Matrix1|.values.get(i) == 4)
{
// replace the 4 found at index i with a 9
vals.set(i, 9)
countNinesInMatrix++
}
}
// Write the ArrayList vals back the Matrix1
|Matrix1|.values = vals
alert(“”Number of 4’s replaced with 9’s: “” + countNinesInMatrix + “” times””)
[/code:2472y52s]
The attached model file contains a simple Matrix named Matrix1 and the code listed here in the notes window of Matrix1.
Let me know if these examples answer you questions.
Dominik