Is the use of match() correct when comparing 2 columns?

[josephinetedesc]

hi all

I have used this formula which I found from another thread and it works beautifully:

Directory;

LOAD Product_Id,

     Product_Line,

     Product_category,

     Product_Subcategory

FROM match.xls (biff, embedded labels, table is match$)

where wildMatch(Product_Line, 'off*', '*ome*');

then I varied by using this expression in a table:

=if(sum(Match(table1Columns1,Table2Columns2)) < 1,'NoMatch in Column2', 'Match in Column2')

This also worked beautifully, however not if the records are not aligned as explained below.

Is it a prerequisite that the columns must be as per example1 and not as example2?

Example1        Example 2 

Column 1     Column 2                         Column1     Column2

George     George                              George     Paul

John          John                                 J ohn          Ringo

Ringo       John                                   Ringo        John

What I want to do is compare the 2 columns and if any record (ie John) appears in Column1 then say that it also appears in Column 2.

Jo

[Anil_Babu_Samineni]

For me looks like this?

If(Column1 = Column2, 'Matching','Not Matching')

[josephinetedesc]

I got the following:

column 2	column1	If(column1 = [column 2], 'Matching','Not Matching')
		Not Matching
Paul	John	Not Matching
Ringo	George	Not Matching
John	Ringo	Not Matching
Fred	Bill	Not Matching
George	Paul	Not Matching

[Anil_Babu_Samineni]

Seems Okay

[josephinetedesc]

It is correct if you are checking line by line:

but what I want is if there is John in Column A and John anywhere in Column B - then "MATCHING", but Bill in Column A does not appear anywhere in Column B - so "Not Matching"

Jo

I have found it is possible in Excel - maybe I will need to stick with this?

A Ridiculously easy and fun way to compare 2 lists
Select cells in both lists (select first list, then hold CTRL key and then select the second)
2. Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Press ok.
4. There is nothing do here. Go out and play!
column1	column 2
John	Paul
George	Ringo
Ringo	John
Bill	Fred
Paul	George
Bill	Mark
Janet	william

[Anil_Babu_Samineni]

Can you make it an better example. Let's consider mine

Column1     Column2     Flag

ABC               XYZ               ??

DEF               UVW               ??

GHI                ABC               ??

JKL                GHI               ??

MNO              RST               ??

[josephinetedesc]

Column1     Column2     Flag

ABC               XYZ               there is a Match

DEF               UVW               No Match

GHI                ABC              there is a Match

JKL                GHI               No Match

MNO              RST               No Match

[Anil_Babu_Samineni]

Are you getting something?

If(Column1=WildMatch('*' & Column2 & '*', Column1), 'No Match', 'Match') as Flag

OR

if(WildMatch(Column2 , '*'&Column1&'*') or WildMatch(Column1 , '*'&Column2&'*'),'No Match', 'Match') AS Flag

[luismadriz · Accepted solution]

Hi J,

This is an option using a Mapping Table

Temp:

Load * inline [

Column1,Column2

ABC,XYZ

DEF,UVW

GHI,ABC

JKL,GHI

MNO,RST

];

MappingTable:

Mapping Load Column2,'Match' as Field

Resident Temp;

Data:

Load *,  ApplyMap('MappingTable', Column1, 'No Match') as Match

Resident Temp;   

Drop Table Temp;

[josephinetedesc]

Luis  that worked.! 

I am now going to try and understand what/how  you did it.

I looked at this thread:  https://community.qlik.com/thread/149499 FROM Aushik Makdoom but I think I need to study it some more!  It is acutally more powerful than the vlookup in Excel.

and also : Mapping ‒ QlikView

Example I understand!

// Load mapping table of country codes:

The Lookup table	The table which needs to be "fixed" with the Country	The Result - note "rest of the world" is the "what if there is no code"
map1: mapping LOAD * Inline [ CCode, Country Sw, Sweden Dk, Denmark No, Norway ] ;	// If the country code is not in the mapping table, put Rest of the world Salespersons: LOAD *, ApplyMap('map1', CCode,'Rest of the world') As Country Inline [ CCode, Salesperson Sw, John Sw, Mary Sw, Per Dk, Preben Dk, Olle No, Ole Sf, Risttu] ; // We don't need the CCode anymore Drop Field 'CCode';	Country Salesperson Denmark Olle Denmark Preben Norway Ole Rest of the world Risttu Sweden John Sweden Mary Sweden Per

//SO THIS LOADS THE 2 COLUMNS INTO MEMORY

Temp:
LOAD Column1,
[Column2]
FROM

(ooxml, embedded labels, table is Sheet2);

/*This looks at Column1 and searches for a match in Column2 – the name of the
Column Fred is irrelevant */

MappingTable:
Mapping Load Column2,'There is a match' as Fred
Resident Temp;

/*This applies the Lookup table – and makes a column called MATCHTYPE - "There is no match" is what will happen if Column1 does not have a match in Column2*/
Data:
Load *,  ApplyMap('MappingTable', Column1, 'There is no match') as MATCHTYPE

Resident Temp; 

Drop Table Temp;

Column1	Column2	MATCHTYPE
Bill	Fred	There is no match
Bill	Mark	There is no match
George	Ringo	There is a match
John	Paul	There is a match
Paul	George	There is a match
Ringo	John	There is a match