Using cross() to look for reference values from another project within string data in a main project

Support and Helpdesk
1

Looking to create a new column base on an expression using cross() (or similar) to look for reference values (e.g., ISO alpha_3 Country codes) from another project within raw address data (e.g., '123 Main Street, Anytown, USA') in a main project. Trying to use something like the code below which does not generate any syntax errors, but neither does it return the values as I'd expected.

Ultimately, I want to perform a lookup against three reference columns, Country Common Name (e.g., 'United States'), Alpha-2 code (e.g., 'US') and Alpha-3 Code (e.g., 'USA'), to create a new column containing a list of any/all values found, but need to get one of them working first. This is to be a single step in a multi-step workflow so hoping it can be done without user intervention, facets, etc.

Apologies if this has already been addressed elsewhere (I looked but couldn't find any). Any guidance would be appreciated. Thanks.

forEach( cross("alpaha_3_cd", "ref_iso_cntry_codes"), if( value.toLowercase().contains(cntryRow.cells["alpaha_3_cd"].value.toLowercase()), cntryRow.cells["alpaha_3_cd"].value, "" ) ).join(", ")

2

Hi @DOMcCrea, welcome to the forum! One thing I noticed is that your call to forEach only has two arguments, but I think it expects to get three: the first argument should be the array to loop over (which you have here), but the second argument should be a variable to bind the current object to in the loop. Can you try adding a second argument to bind the loop variable and then use that in your if statement?

1 Like
3

Thanks Rory!
I wasn't really sure if the second argument was to be just a placeholder or some specific value. I modified my formula as per your suggestions (see below)as well as correcting a typo I'd missed ([alapha_3_cd] vs [alpha_3_cd]) in the column name, both in the reference table and the formula). I still do not get any syntax errors but, alas, no output either. Any further observations or suggestions would be appreciated.

forEach( cross("alpha_3_cd", "ref_iso_cntry_codes"), alpha_3_cd, if( value.toLowercase().contains(cntryRow.cells["alpha_3_cd"].value.toLowercase()), cntryRow.cells["alpha_3_cd"].value, "" ) ).join(", ")

4

The thing with cross is, that it is basically an ID based lookup.

Think: "Give me all the rows of a project where in column X is exactly the given value."

What you are looking for is: "Is one the values in column Y part of the address string."

Sometimes you have a delimiter in the address string so you can fake the exact value lookup.
The following GREL snippet is using , as delimiter to split the address string into several search tokens and then tries to lookup these tokens in the column of another project.

forEach(
    value.toLowercase().split(", "),
    searchToken,
    searchToken.cross("Name of other project", "Name of other column").cells["Name of other column"].value.join(", ")
).join(", ")

But it all depends on whether you have a valid delimiter to split the address string into valid search tokens.