topic Find non matching values in two fields in QlikView

Find non matching values in two fields

Sat, 06 Feb 2016 09:26:30 GMT

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.

Re: Find non matching values in two fields

sunny_talwar — Sat, 06 Feb 2016 10:30:15 GMT

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?

Re: Find non matching values in two fields

swuehl — Sat, 06 Feb 2016 11:25:39 GMT

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;

Re: Find non matching values in two fields

Sat, 06 Feb 2016 14:10:24 GMT

They will both come from different tables

Re: Find non matching values in two fields

swuehl — Sat, 06 Feb 2016 14:34:27 GMT

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;

Re: Find non matching values in two fields

MarcoWedel — Sun, 07 Feb 2016 01:12:49 GMT

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

Re: Find non matching values in two fields

maxgro — Sun, 07 Feb 2016 07:48:59 GMT

INPUT1:

LOAD * INLINE [

Field1

];

INPUT2:

LOAD * INLINE [

Field2

];

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;

Re: Find non matching values in two fields

MarcoWedel — Mon, 08 Feb 2016 00:13:24 GMT

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');