Match values in two separate tables using match and concat function

[Anonymous]

Hello Everyone,

I am trying to compare two fields (loaded in separate tables) which I am trying to compare using a combination of the Match() and Concat() functions.

I would like to know whenever a value from field B also belongs in field A. Here is what I am using so far.

=if(match(A, concat(B, ',')) > 0, 1, 0)

There are several values from B which belong to A, but this function returns all 0's. This function works when I hard code values and remove concat(B, ','). I put concat(B, ',') into a text box just to check and it does return all values of B separated by a comma.

Any ideas what I might be missing?

Thanks in advance.

Michael

[swuehl · Accepted solution]

Maybe like

=Sum( Match(A,B))

[sunny_talwar]

May be this:

=Concat(Aggr(If(SubStringCount(Concat(DISTINCT B, ','), A) = 1, A), A), ', ')

for a list of As that are in Bs

=Count(Aggr(If(SubStringCount(Concat(DISTINCT B, ','), A) = 1, A), A))

for count of As that are in Bs

[swuehl]

Or for a list of Bs that are in As :

=Concat( If(Match( A, B), B),', ')

[sunny_talwar]

Damn it!!! You are 'THE' expert

[swuehl]

Or using set analysis

=Concat( {<A = p(B) >} A, ', ')

[Anonymous]

Thanks for the responses. Adding sum() did the trick.

I don't fully understand why, but it seems to be working now.

[swuehl]

In general, always use aggregation functions.

Use Aggregation Functions!

in your original expression,

=if(match(A, concat(B, ',')) > 0, 1, 0)

value of field A was not unambiguous, it couldn't resolved to a unique value by QV.

The sum() function in your case, having fields in two tables that are not linked to each other, will build a cross join of the two fields, creating a temporary table with each value of A against each value of B.

That's why

=Sum( Match(A,B))

creates a count of all matching A-B combinations in that temporary table (Match() will return 1 in these cases).

Hope this helps,

Stefan