Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
dmac1971
Contributor II

Fuzzy Match or Similar?

Lets say I have 2 cols, I want to see if any word in col2 appear in col1?  If a word matches in both then flag it etc.

1 Solution

Accepted Solutions
pokassov
Valued Contributor

Re: Fuzzy Match or Similar?

Hello!

//--- original table

t1:

LOAD * Inline [

id, t1, t2

1, John Hello, Max Bye John

2, Frank, Piter

];

//--- separate first col by words

t2:

LOAD

id,

SubField(t1,' ') as s1

Resident t1;

//--- separate second col by words

t3:

LOAD

id,

SubField(t2,' ') as s2

Resident t1;

//--- find out the same words

Inner Join (t2)

load

  id, s2 as s1

Resident t3;

DROP Table t3;

//--- if col1 and col2 have more then 1 same word - leave only one id per row

t3:

LOAD

  Distinct id,

  1 as flag

Resident t2;

DROP table t2;

//--- add flag to original table

Left Join (t1)

LOAD

  id, flag

Resident t3;

DROP Table t3;

View solution in original post

4 Replies
Siva_Sankar
Honored Contributor

Re: Fuzzy Match or Similar?

Find the attached application and let me know whether this is what you want.

dmac1971
Contributor II

Re: Fuzzy Match or Similar?

Not really I'm afraid.  Both cols contain a number of words within a text string, if it was single chars or single words in both that would be fairly easy to do.  Thanks though!

pokassov
Valued Contributor

Re: Fuzzy Match or Similar?

Hello!

//--- original table

t1:

LOAD * Inline [

id, t1, t2

1, John Hello, Max Bye John

2, Frank, Piter

];

//--- separate first col by words

t2:

LOAD

id,

SubField(t1,' ') as s1

Resident t1;

//--- separate second col by words

t3:

LOAD

id,

SubField(t2,' ') as s2

Resident t1;

//--- find out the same words

Inner Join (t2)

load

  id, s2 as s1

Resident t3;

DROP Table t3;

//--- if col1 and col2 have more then 1 same word - leave only one id per row

t3:

LOAD

  Distinct id,

  1 as flag

Resident t2;

DROP table t2;

//--- add flag to original table

Left Join (t1)

LOAD

  id, flag

Resident t3;

DROP Table t3;

View solution in original post

dmac1971
Contributor II

Re: Fuzzy Match or Similar?

Used the Subfield method and then marked each record as Y/N match.  Thanks.!