topic Re: Match fields from two different tables in QlikView

Match fields from two different tables

Anonymous — Wed, 23 Jul 2014 08:59:53 GMT

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.

Re: Match fields from two different tables

marcus_malinow — Wed, 23 Jul 2014 09:06:32 GMT

Hi Colin,

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

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

Marcus

Re: Match fields from two different tables

lironbaram — Wed, 23 Jul 2014 09:08:27 GMT

this expression will work

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

Re: Match fields from two different tables

Anonymous — Wed, 23 Jul 2014 09:12:16 GMT

Hi Marcus,

That worked perfectly. Thanks very much.

Much appreciated.

Declan

Re: Match fields from two different tables

Anonymous — Wed, 23 Jul 2014 09:17:54 GMT

That also worked Liron.

Thanks very much.

Declan