Skip to main content

New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
HelloQlik
Contributor II
Contributor II

Qlik sense Flag

Hello Experts

Hi guys, I am stuck in issue
I have list of key words in Column A which should match with the B column and flag as match or No Match.

NOTE: A column will have 100 key words and B column has 1 million approx..So its not a row to row comparision rather each key word from Column A should conpare against entire B column and flag.

 

20201119_234249.jpg

 Thanking you in Advance.

sharing an example as screenshot

6 Replies
Spivey
Partner - Contributor III
Partner - Contributor III

There isn't a super great way to do this but here's one way that uses the Concat() script function to create a list of all the values in [Column A], assigns that list to a variable, and then uses that in a WildMatch() function:

[words]:
LOAD * Inline [
colA, colB
USA, COMPLETE YEAR
ARGENTINA, INWORD INDIA / COUNTRY
INDIA, PYTHON PROGRAMMING
ALGERIA, JAVA PROGRAMMING
, C PROGRAMMING IN ALGERIA
, OUTWORD USA IN THE COUNTRY
, OUTWORD USA IN THE COUNTRY
];

[concatTbl]:
LOAD DISTINCT
	chr(39) & '*' &  Concat(distinct [colA], '*' & chr(39) & ',' & chr(39) & '*')  & '*' & chr(39) as [colA_concat]
Resident [words]
  WHERE len([colA]) <> 0;

Let vConcat = peek('colA_concat', 0, 'concatTbl');
Drop Table [concatTbl];

[words_matched]:
LOAD DISTINCT
    [colB]
  , If( WildMatch([colB], $(vConcat)) > 0, 'MATCH', 'NO MATCH')  as [Match?]
Resident [words];

Drop Table [words];

 

HelloQlik
Contributor II
Contributor II
Author

Thank you so very much Spivey ..Let me give a try and will confirm back..its really great help.

 

One quick question my data is in Excel and it has two columns which connected to qlik and the solution ia used as inline table...how can I change this script to my requirement with columns.

Thanks a Tonnssss

Spivey
Partner - Contributor III
Partner - Contributor III

Sure thing -- you can ignore my inline table, just change [colA] to the column name from your Excel file that have the search terms, change [colB] to the column name from your Excel file that has the phrases to search in, and then change the [words] table name to whatever the table loaded from your Excel file is called.

HelloQlik
Contributor II
Contributor II
Author

Thanks...Am really excited to implement your solution...Thanks 

rwunderlich

Here's an example that does just what you ask for. 

Qlikview Cookbook: Indexing Keywords in Text

-Rob

HelloQlik
Contributor II
Contributor II
Author

Hello Spivey.

 

Your solution is perfectly working as in line table ..but I have another issue...ColA is coming Tables and Colb is coming from Table B.

 

Any suggestion..really appropriated.