cell.cross function issue

[Sara Lightowlers]

Hi,

I'm trying to copy a column from one datasheet to another based on a matching column. When I use the expression cell.cross("Import Low Path Review", "Biorepository ID").cells["Overall Cellularity"].value[0] I get the following error - Error: java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.String

I've also tried if (value!='null',cell.cross("Import Low Path Review", "Biorepository ID").cells["Overall Cellularity"].value[0],'') and had the same error.

Does anyone know how I can fix this?

[Mahdi Shariff]

I'm also having issues with this functionality - even though it used to work fine.

It shows it processes correctly on the preview:

But after it still doesn't actually work:

Mahdi

--
You received this message because you are subscribed to the Google Groups "OpenRefine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Owen Stephens]

Hi Sara,

Which version of OpenRefine are you using? I think that this particular issue should have been fixed in the 3.1 release, so if you aren't already using 3.1 please try upgrading and see if it fixes the problem.

If you are already using 3.1 or the problem still occurs after upgrading then let us know and we can investigate further.

Owen

[Owen Stephens]

Hi Mahdi,

This looks like a different issue to the one Sara is having. I can see in your preview you are getting an array from the cross function like:

["Director of Sales"]

You can't store Arrays in cells in OpenRefine, so you have to convert to a storable value - usually a string - to actually store the result - otherwise you just get empty cells.

So try adding:

.join("|")

to the end of your GREL expression and that should give you a result where the new column is populated correctly

Owen

[Mahdi Shariff]

Thanks Owen - very helpful.

In followup - if I want to use a similar formula but add it into an existing column (i.e rather than add a new column based on the column) how would I achieve this?

Mahdi 

[Owen Stephens]

Hi Mahdi

That would be done in a very similar way - just use the "Edit Cells"->"Transform" option to write the transformation.

If you want to combined the looked up value with the value already in the cell you can using something like:

value + cell.cross("project1","Column").cells["Col"].value.join("|")

 
or something similar

Owen

[Mahdi Shariff]

Apologies I miscommunicated the question:

Imagine I have 2 projects:

Project 1:

Name | Email | Position | Organisation

Project 2:

Name | Email Address | Organisation 

---

Normally I would:

Select Project 1 'Email' > Edit Column >  Add Column based on this Column > cell.cross("Project 2", "Email Address").cells["Organisation"].value[0] 

- Name it Column 'Organisation 2'

- Facet by Blank on 'Organisation'

- Transform on 'Organistion' cells["Organisation 2"].value

- Delete 'Organisation 2'

However:
if I have a pre-existing Organisational Column in Project 1 with some values, I would want to:

- Facet by blank

- Select blank, and then run the lookup (Rather than creating a new column as above)

How would this be achieved in one step? I'm assuming I just need to add something to say which first column to use as the matching column.

Hopefully that makes sense 

[Owen Stephens]

On Monday, January 14, 2019 at 1:52:42 PM UTC, Mahdi Shariff wrote:

Imagine I have 2 projects:

Project 1:

Name | Email | Position | Organisation

Project 2:

Name | Email Address | Organisation 

---

Normally I would:

Select Project 1 'Email' > Edit Column >  Add Column based on this Column > cell.cross("Project 2", "Email Address").cells["Organisation"].value[0] 

- Name it Column 'Organisation 2'

- Facet by Blank on 'Organisation'

- Transform on 'Organistion' cells["Organisation 2"].value

- Delete 'Organisation 2'

However:
if I have a pre-existing Organisational Column in Project 1 with some values, I would want to:

- Facet by blank

- Select blank, and then run the lookup (Rather than creating a new column as above)

How would this be achieved in one step? I'm assuming I just need to add something to say which first column to use as the matching column.

So you can refer to other columns using the syntax:

cells["Column name"]

So in the Organisation column you could use the GREL

cells["Email"].cross("Project 2", "Email Address").cells["Organisation"].value[0]

I hope that makes sense?

Owen

 

[Matt D]

I have a similar problem with the cross() function in Version 3.1 [b90e413]

This does work, using Edit Column>Add Column Based On This Column:

cell.cross("ProjectB","join_column_in_ProjectB").cells["related_column_in_ProjectB"].value[0]

This here seems like it should work, but doesn't work when doing Edit Cells>Transform:

cells["join_column_in_ProjectA_not_transform_column"].cross("ProjectB","join_column_in_ProjectB").cells["related_column_in_ProjectB"].value[0]

When I do

cells["Column"].value

it works--I get values, and without ".value", I get

[Object cell]

which is the same kind of output when I just do

cell

so it seems that cross() does not like the cell reference coming from cells().

[Thad Guidry]

So, OpenRefine has Variables, Controls, & Functions to work with in GREL syntax. https://github.com/OpenRefine/OpenRefine/wiki/General-Refine-Expression-Language

Variables we have described here: https://github.com/OpenRefine/OpenRefine/wiki/Variables

Functions are described here: https://github.com/OpenRefine/OpenRefine/wiki/GREL-Functions

Controls are here: https://github.com/OpenRefine/OpenRefine/wiki/GREL-Controls

If you are trying to extract more than 1 value from the result of cross, then you'll need to iterate over the array with forEach()

https://github.com/OpenRefine/OpenRefine/wiki/GREL-Other-Functions#crosscell-c-string-projectname-string-columnname

Thad

https://www.linkedin.com/in/thadguidry/

--

[Owen Stephens]

Matt D - this looks like the same issue that was reported by Mathieu recently - https://github.com/OpenRefine/OpenRefine/issues/1950

While there is more investigation to do, on the surface it seems that currently you have to start the 'cross' function from the column that contains the values you want to use as part of your 'cross' lookup