Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Find the attached application and let me know whether this is what you want.
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!
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;
Used the Subfield method and then marked each record as Y/N match. Thanks.!