Match fields from two different tables

[Anonymous]

Hi all,

I have an issue when trying to return a value, 'Yes' or 'No', based on whether the value of a field (Field1) from one table appears in the field (Field2) of another table.

The tables appear like this:

Table1:

Training	Field1
ABC	John
ABC	Mike
ABC	Mary
ABC	Tom

Table2:

Training	Field2
ABC	John
ABC	Pat
ABC	Tom

Basically, I'd like my chart to display with Training and Field1 as dimensions with the result 'Yes' or 'No' as a calculated expression called Complete if the value of Field1 matches Field2:

Training	Field1	Complete
ABC	John	Yes
ABC	Mike	No
ABC	Mary	No
ABC	Tom	Yes

I can get it to work generally using an IF or IF(Match) statement, but only if both fields are selected as dimensions in the chart.

=If(Match(Field1,Field2), 'Yes', 'No')

The problem with this is that it also displays all incorrect values, essentially duplicating each result based on the number of entries in Field2.

E.g:

Training	Field1	Field2	Complete
ABC	John	John	Yes
ABC	John	Pat	No
ABC	John	Tom	No
ABC	Mike	John	No
ABC	Mike	Pat	No
ABC	Mike	Tom	No

etc.

If I remove Field2 as a dimension from the chart, then it returns 'No' for all entries in Field1.

Is there a solution to have the chart display as I want above?

Thanks.

[marcus_malinow · Accepted solution]

Hi Colin,

given your data structure, you should be able to use something like this:

If(index('|' & concat(distinct Field2,'|') & '|', '|' & only(Field1) & '|')>0, 'Yes','No')

Make sure though that you only use Training and Field1 as your dimensions, otherwise you'll end up with the Cartesian join issue.

Marcus

[lironbaram]

hi

this expression will work

if(wildmatch(Concat(Field2,','),'*' & Field1 &'*'),'YES','NO')

[Anonymous]

Hi Marcus,

That worked perfectly. Thanks very much.

Much appreciated.

Declan

[Anonymous]

That also worked Liron.

Thanks very much.

Declan