Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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;

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;

dmac1971
Contributor II

Re: Fuzzy Match or Similar?

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

Community Browser