Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
flames
Contributor III
Contributor III

match each value in a column with another column which contains column1 as one word or pattern match

Hi I have two columns in two excels...

excel1 
---------

COL1
WIRE
DISH
BLANKET
BAG
DO
HNET
INTNET
KITCHEN

 

excel2

---------

COL2
NOMATCHING
WIRED
WIRE TECHNOLOGIES
DISH NETWORKS
BAGGAGE
DOING
INTERNET
GOOGLE
APPLE
NETFLIX
FACEBOOK
AMAZON
DELL
MAC
CLEAR
JNJ
CAT
JIO
LEVIS

 

Now i need to check if each word from COL1 as a match %like% in COL2 .. result should be like this,,,

 

COL2COL3
NOMATCHING 
WIREDWIRE
WIRE TECHNOLOGIESWIRE
DISH NETWORKSDISH
BAGGAGEBAG
DOINGDO
INTERNET 
GOOGLE 
APPLE 
NETFLIX 
FACEBOOK 
AMAZON 
KITCHENWAREKITCHEN
MAC 
CLEAR 
JNJ 
CAT 
JIO 
HNET TECHHNET

 

appreciate your help. 

3 Replies
Dalton_Ruer
Employee
Employee

Wildmatch will be your friend. 

saran7de
Master
Master

Try this,

COL1:
LOAD * INLINE [
    COL1
    WIRE
    DISH
    BLANKET
    BAG
    DO
    HNET
    INTNET
    KITCHEN
];

COL2:
LOAD * INLINE [
    COL2
    NOMATCHING
    WIRED
    WIRE TECHNOLOGIES
    DISH NETWORKS
    BAGGAGE
    DOING
    INTERNET
    GOOGLE
    APPLE
    NETFLIX
    FACEBOOK
    AMAZON
    DELL
    MAC
    CLEAR
    JNJ
    CAT
    JIO
    LEVIS
];
saran7de
Master
Master

Dim:

COL2

Exp:

Pick(WildMatch(COL2,$(=Concat('''*'&COL1&'*''',','))),$(=Concat(Chr(39)&COL1&Chr(39),',')))

Output:

commQV60.PNG