Joining multi-valued cells as an expression

Support and Helpdesk Data cleaning and transformations
1

Hello

Individual columns have a menu option to "join multi valued cells". Does anyone know what this looks like as a GREL expression so that I can apply it as a transform to all columns in one action? (I'm lagging behind on v3.2)

Thanks, Graham

2

Hi @grahamjevon. Unfortunately the join multi-valued cells function isn't a front for a GREL expression, but rather coded into the OR software. I think you could write a GREL that did the "join" part in GREL by:

  • checking if you are in the first row in the record
    • If yes, populate with a "join()" across all rows in the record
    • If not, set to blank or null

But this would be a relatively complex GREL statement and will leave you with a whole load of blank cells as the blanked rows won't be removed by this GREL

A different approach which might prove a better option would be to create a "history" file that applies the "join multi-valued cells" to each column in turn. This basically looks like:

[
  {
    "op": "core/multivalued-cell-join",
    "columnName": "Column 2",
    "keyColumnName": "Column 1",
    "separator": "|",
    "description": "Join multi-valued cells in column Column 2"
  },
{
    "op": "core/multivalued-cell-join",
    "columnName": "Column 3",
    "keyColumnName": "Column 1",
    "separator": "|",
    "description": "Join multi-valued cells in column Column 3"
  },
{
    "op": "core/multivalued-cell-join",
    "columnName": "Column 4",
    "keyColumnName": "Column 1",
    "separator": "|",
    "description": "Join multi-valued cells in column Column 4"
  }
]

etc. Once you have this JSON (there are of course various ways of creating this), you can apply in via the undo/redo pane as usual

3

Thanks @ostephens , in that case I'll go for the json option (or export to perform this action in Python and import back to OR).