Excel Keywords with Description column (Free text) match Standardization in Qliksense

[SK28]

Hi,

I have an Excel Sheet with has 30 to 40 Keywords in it.

and in Qlik data model I have a Free text column.

My requirement here is if a User updates the new keywords (/Existing keywords), It has to search those keywords in excel sheet and match with the FREE TEXT COLUMN(Description) in Qlik datamodel, if matches then it has to FLAG as KEYWORD column or N/A

Example:

Keywords: --> in excel

Build

Test

Check

funds

costs

.... so on I have some keywords in that excel

and in Qlik data model:

I have a Description name( Free text Column)

Costs of the phone is so & so

Build the factory

check the level of the gas pipeline

Test the performance of the phone

... so and so

Excepted Output:

Description	Flag
Costs of the phone is so & so	Costs
To perform ground analysis	N/A
Build the factory	Build
check the level of the gas pipeline	Check
Test the performance of the phone	Test
To perform operations	N/A

This is my expected results, Is this possible.

as the user updates the EXCEL KEYWORD FILE, after refreshing the Qliksense APP. This FLAG column has to be refreshed accordingly

Thanks in Advance.

[chaorenzhu]

A few questions to clarify: What if multiple Keywords in Description? Must be exact match (uppercase/lowercase)? For example if Keywords contain "Build" but Description is "he builds a team", still considered match?

[Digvijay_Singh · Accepted solution]

Some ideas like this, you may need to refine it for your use I guess - 

excel_map:
mapping Load UPPER(keyword) as Word1, UPPER(keyword) as Word2 inline [
keyword
Build
Test
check
funds
Costs
];

datamodel:
Load distinct Description,
Applymap('excel_map',desc_word,'N/A') as keyword
;
Load *, UPPER(Subfield(Description,' ')) as desc_word;
Load * inline [
Description
Costs of the phone is so & so
Build the factory
Check the level of the gas pipeline
Test the performance of the phone
To perform ground analysis
To perform operations
];

Left Join(datamodel)
Load Description,
Count(Description) as CountRows
resident datamodel
Group By Description
;

NoConcatenate
Final:
Load Description, Capitalize(keyword) as keyword
resident datamodel
Where NOT (keyword='N/A' and CountRows>1)
;
Drop table datamodel;