Find non matching values in two fields

[#1]

hi I need to find matching and non matching values in two fieds Field1 and Field2

Field1	Field2
aa	cc
bb	ee
cc	ff
dd	aa
gh	kk
	ff

expected output with two listboxes

Matching in Both	Non Matching
aa	bb
cc	dd
	gh
	ee
	ff
	kk
	ff

Also should be able to answer these

1. Values which are not matching belong to which field if i click on these

2. Option to find a value and make out which field it belong to.

[sunny_talwar]

Are these two fields come from the same table or different table? Are these completely independent of each other? Would you be able to share a sample?

[swuehl]

Maybe like attached, using a script approach (hence Field1 and Field2 could also resisde in different tables):

INPUT:

LOAD * INLINE [

Field1, Field2

aa, cc

bb, ee

cc, ff

dd, aa

gh, kk

ff,

];

FILTER:

LOAD DISTINCT Field1 as Value, 'Field1' as Origin

RESIDENT INPUT;

LOAD DISTINCT Field2 as Value, 'Field2' as Origin

RESIDENT INPUT;

LEFT JOIN (FILTER)

LOAD Value, Count(Value) as Count

RESIDENT FILTER

GROUP BY Value;

[#4]

They will both come from different tables

[swuehl]

Another approach with fields in different tables and links to the filter table.

INPUT1:
LOAD * INLINE [
Field1
aa
bb
cc
dd
gh
ff
];


INPUT2:
LOAD * INLINE [
Field2
cc
ee
ff
aa
kk
];




TMP:
LOAD DISTINCT Field1 as Value, 'Field1' as Origin, Field1
RESIDENT INPUT1;


CONCATENATE
LOAD DISTINCT Field2 as Value, 'Field2' as Origin, Field2
RESIDENT INPUT2;


FILTER:
NOCONCATENATE
LOAD Value, Only(Field1) as Field1, Only(Field2) as Field2, If(Count(Value)>1,'Both',Only(Origin)) as Origin
RESIDENT TMP
GROUP BY Value;


DROP TABLE TMP;

[MarcoWedel]

Hi,

one solution could be:

SET vLoadFieldMatch = LOAD Distinct
                           $1 as Field,
                           '$1' as SourceField,
                           -Exists($2,$1) as IsMatching,
                           If(not Exists($2,$1),'Non ')&'Matching' as MatchState,
                           If(Exists($2,$1),$1) as Matching,
                           If(not Exists($2,$1),$1) as NonMatching
                      Resident $3;

table1:
LOAD Field1 FROM [https://community.qlik.com/thread/204514] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 7))));

table2:
LOAD Field2 FROM [https://community.qlik.com/thread/204514] (html, codepage is 1252, embedded labels, table is @1);

tabMatch:
$(vLoadFieldMatch(Field1,Field2,table1));
$(vLoadFieldMatch(Field2,Field1,table2));

hope this helps

regards

Marco

[maxgro]

INPUT1: 

LOAD * INLINE [ 

Field1 

aa 

bb 

cc 

dd 

gh 

ff 

aa

]; 

INPUT2: 

LOAD * INLINE [ 

Field2 

cc 

ee 

ff 

aa 

kk 

]; 

TMP:                 LOAD  Distinct Field1 as Value, 1 as FromField1                                Resident INPUT1; 

join (TMP)       LOAD  Distinct Field2 as Value, 1 as FromField2                                 Resident INPUT2; 

left join (TMP) LOAD Value, if(FromField1=FromField2, 'MATCH', 'NOT MATCH') as Match Resident TMP;

[MarcoWedel]

same with subroutine rather than parameterized variable:

SUB LoadFieldMatch (F1, F2, T1,T2)
    $(T2):
    LOAD *,
         If(not IsMatching,'Non ')&'Matching' as MatchState,
         If(IsMatching,Field) as Matching,
         If(not IsMatching,Field) as NonMatching;
    LOAD Distinct
         $(F1) as Field,
         '$(F1)' as SourceField,
         -Exists($(F2),$(F1)) as IsMatching
  Resident $(T1);
ENDSUB;

table1:
LOAD Field1 FROM [https://community.qlik.com/thread/204514] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 7))));

table2:
LOAD Field2 FROM [https://community.qlik.com/thread/204514] (html, codepage is 1252, embedded labels, table is @1);

CALL LoadFieldMatch('Field1','Field2','table1','tabMatch');
CALL LoadFieldMatch('Field2','Field1','table2','tabMatch');