Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Find non matching values in two fields

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

 

Field1Field2
aacc
bbee
ccff
ddaa
ghkk
ff

expected output with two listboxes

  

Matching in BothNon Matching
aabb
ccdd
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.

7 Replies
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
MVP
MVP

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;

Not applicable
Author

They will both come from different tables

swuehl
MVP
MVP

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:

QlikCommunity_Thread_204514_Pic1.JPG

QlikCommunity_Thread_204514_Pic2.JPG

QlikCommunity_Thread_204514_Pic3.JPG

QlikCommunity_Thread_204514_Pic4.JPG

QlikCommunity_Thread_204514_Pic5.JPG

QlikCommunity_Thread_204514_Pic6.JPG

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
MVP
MVP

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;

1.png

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