Dear Pro Refiners,
When creating a new column, how do I index, within a record, only those lines containing a value?
Surprisingly, I've been struggling with this problem for hours.
Salut Julien,
I would do it in 5 steps.
- Create a backup column of the index
- Permanently reorder the column with missing values so that the empty elements are at the end.
- Hide them with a facet
- Do you command to create the new column with your data
- Using the saved index, reorder permenently the rows to the original order.
Bonne chance, Antoine
Curious, why do you feel you have to "index" within a record when it is always provided? There's always an index of rows from/to in a record and the number of rows, and the array of cells in a given column of a record.
Perhaps we/you could add a paragraph that might help others with what you feel is missing and important in our Records documentation:
But maybe you want to show the row.record.index ONLY if the columns value is not blank?
forNonBlank(value,v,row.record.index,null)
Otherwise, if you are trying to RE-index, then like @Antoine2711 is hinting at, you will need to push this out to a new column.
@thadguidry, thanks for the replies. Indeed, I want to apply the indexing only to the column values that are not blank. However, my aim here is to achieve (sub)level indexing, i.e. instead of 1,1,1,1,1... 2,2,2,2,2... to obtain 1,2,3,4,5,6... 1,2,3,4,5,6... on non-blank values only.
Salut @Antoine2711, thanks for the reply. I’ll try that route. I have many similar tables to which I have to add this column, which is why I was looking for a more straightforward solution.
@archilecteur are your values in column unique (i.e. within a single record, would have repetitions of the same value in the web-scraper-order column)?
If they are you can "Add column based on this column" with the transformation:
forEach(forEachIndex(row.record.cells["web-scraper-order"].value,i,v,v.toString()+"|"+(i+1).toString()),w,if(w.split("|")[0]==value,w.split("|")[1],null)).join("|")
To break this down a bit:
row.record.cells["web-scraper-order"].value gives you an array of all the non-null values for this column within the current record.
So
forEachIndex(row.record.cells["web-scraper-order"].value,i,v,v.toString()+"|"+(i+1).toString())
builds an array that concatenates (as a string) the value from the cell with the place (index) of that value in the list of non-null values in the record (we add one to the index to count from 1 instead of zero)
So if we start with something like:
| Column 1 | Column 2 |
|---|---|
| 1 | a |
| null | |
| b | |
| c | |
| 2 | d |
then forEachIndex(row.record.cells["Column 2"].value,i,v,v.toString()+"|"+(i+1).toString()) gives an output of ["a|1","b|1","c|3"] for rows 1-4 (record 1), and ["d|1"] for row 5 (record 2)
The outer forEach then iterates through this array created by the forEachIndex and if the value in the current cell matches the first part of a value in the array (i.e. we are in the right row), it stores the index (split out from the string), otherwise it stores a null
Finally join is used to get a single string output from the array, ignoring any null values. So we get something like:
| Column 1 | Column 2 | Output of forEachIndex | Output of forEach | Output of join (final output) |
|---|---|---|---|---|
| 1 | a | ["a|1","b|2","c|3"] | [1,null,null] | 1 |
| null | ["a|1","b|2","c|3"] | [null,null,null] | null | |
| b | ["a|1","b|2","c|3"] | [null,2,null] | 2 | |
| c | ["a|1","b|2","c|3"] | [null,null,3] | 3 | |
| 2 | d | ["d|1"] | [1] | 1 |
You can, of course, add a final .toNumber() on the end of the expression to get a sortable integer if that's what you need.
This approach fails if you have repeated values in the column as your forEach will result in an array with more than one non-null entry. I'm not sure what the solution would be in that case, although my instinct is that there's probably a way around it given some more thought.
@Antoine2711, it works as expected. Thanks again for the help.
1 Like
@ostephens, all of the identifiers from web-scraper should be unique. However, there may be one or many blank values per record, which is a repetition technically.
@archilecteur - sorry I should have been clear I meant any repeated non-null values.
all of the identifiers from web-scraper should be unique
In that case adding a column using
forEach(forEachIndex(row.record.cells["web-scraper-order"].value,i,v,v.toString()+"|"+(i+1).toString()),w,if(w.split("|")[0]==value,w.split("|")[1],null)).join("|")
should work for you
Damn. You did it again @ostephens.
Thank you.
1 Like