topic Re: Match values in two separate tables using match and concat function in QlikView

Match values in two separate tables using match and concat function

Anonymous — Tue, 29 Dec 2015 17:56:43 GMT

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

Re: Match values in two separate tables using match and concat function

swuehl — Tue, 29 Dec 2015 18:29:28 GMT

Maybe like

=Sum( Match(A,B))

Re: Match values in two separate tables using match and concat function

sunny_talwar — Tue, 29 Dec 2015 19:31:55 GMT

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

Re: Match values in two separate tables using match and concat function

swuehl — Tue, 29 Dec 2015 19:41:38 GMT

Or for a list of Bs that are in As :

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

Re: Match values in two separate tables using match and concat function

sunny_talwar — Tue, 29 Dec 2015 19:43:24 GMT

Damn it!!! You are 'THE' expert

Re: Match values in two separate tables using match and concat function

swuehl — Tue, 29 Dec 2015 20:12:50 GMT

Or using set analysis

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

Re: Match values in two separate tables using match and concat function

Anonymous — Thu, 31 Dec 2015 17:26:39 GMT

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

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

Re: Match values in two separate tables using match and concat function

swuehl — Thu, 31 Dec 2015 18:10:13 GMT

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