Having problems with the cell.cross() function

[Antoine Beaubien]

Hi users of OR,

   I'm not sure if I'm not using well the cell.cross() function, I can seam to be able to use it in many situations. Here's a few examples.

I have these 2 projects, pretty simple. 

#1 TEST-Provinces : Prov_ID, Prov_DIM, Prov_Lfr, Prov_WDID, Prov_CoordGeo

#2 TEST-Personnes : Pers_ID, Pers_Lfr, Pers_Ville, Prov_DIM

By using the column Prov_DIM as the key, I want to cross some data from one table to the other. But I can't, either with « Add column based on this column... » nor « Transform... ».

Here's my small data sample and a couple of screenshots.

#1 : trying to get data of an external project (fails), using « Add column based on this column... »: 

cell.cross("TEST-Provinces", "Prov_DIM")

#2 : trying to get data from the same project using the same key as #1 (succede), using « Add column based on this column... »:

cell.cross("TEST-Personnes", "Prov_DIM")

Just for the context, here a screenshot of the other project:

Am I doing something wrong? The documentation does not indicate anything problematic with what I want to do.

Regards,

   Antoine

[Antoine Beaubien]

Here's a better version that correct some data integrity issues (2 bad values).

Regards, A.

[Antoine Beaubien]

Also not working if using a string value (cell.value, or just "QC").

Le jeudi 19 mars 2020 23:47:19 UTC-4, Antoine Beaubien a écrit :

Hi users of OR,

   I'm not sure if I'm not using well the cell.cross() function, I can seam to be able to use it in many situations. Here's a few examples.

I have these 2 projects, pretty simple. 

#1 TEST-Provinces : Prov_ID, Prov_DIM, Prov_Lfr, Prov_WDID, Prov_CoordGeo

#2 TEST-Personnes : Pers_ID, Pers_Lfr, Pers_Ville, Prov_DIM

By using the column Prov_DIM as the key, I want to cross some data from one table to the other. But I can't, either with « Add column based on this column... » nor « Transform... ».

Here's my small data sample and a couple of screenshots.

#1 : trying to get data of an external project (fails), using « Add column based on this column... »: 

cell.cross("TEST-Provinces", "Prov_DIM")

#2 : trying to get data from the same project using the same key as #1 (succede), using « Add column based on this column... »:

cell.cross("TEST-Personnes", "Prov_DIM")

Just for the context, here a screenshot of the other project:

[Lu Liu]

Hey Antoine, you need to trim "Prov_DIM" in both tables first. 

You can trim by 

1. select "Prov_DIM"

2. Edit cells

3. Transform

4. change the grel from "value" to "trim(value)"

After this, you can cross successfully.

Notice the first two errors occured because there are no "Prov_DIM" with value "PQ" in "TEST-Provinces".

Hope this is helpful!

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

[Lu Liu]

Oh, the new data for Personnes you provided solves the "IndexOutOfBoundsException":

[Antoine Beaubien]

Yes! Thank you, Lu, it was an obvious mistake. Thank for seeing it.

Now, the real questions are coming… ;-)

So, if I create a new column with « Add column based on this column... » I have my data, with your corrections to my dataset and this Grel query: cross(value, "TEST-Provinces", "Prov_DIM")[0].cells["Prov_WDID"].value. 

Now, if I clear the row after, and I try to get the same data again with the same query (but in the context of « Transform... », I get null values.

#1 :

cells.Prov_DIM.cross("TEST-Provinces", "Prov_DIM")

#2 :  

cells.Prov_DIM.value.cross("TEST-Provinces", "Prov_DIM")[0].cells["Prov_WDID"] or even cells.Prov_DIM.value.cross("TEST-Provinces", "Prov_DIM") 

I think I've read something from Antonin about the cell context, but there is nothing in the documentation about that.

What do you think of that?

 
Regards, Antoine

[Owen Stephens]

Yes unfortunately the cross function does not work if you use `cells` to reference another cell - I'm not sure exactly why this fails, although there is discussion at https://github.com/OpenRefine/OpenRefine/issues/1950

Owen

[Lu Liu]

I am looking at the code to find out why this fails, your post is really helpful. I believe I can gei it fixed :)

[Antoine Beaubien]

@Owen, 

   I really wonder: why can we provide only a string value as the first parameter if that function is bound to a cell? Should'n cross, the way it's documented, handle cross("QC", "TEST-Provinces", "Prov_DIM")?

   Also, if what you say is thru, then this cross function can never be used somewhere else the in Add column based on this column...? Because a Transform is not bound to a particular cell…

@Lu: glad my information is helpful. Thank for your involvment.

Thanks for your input, regards,

   Antoine