
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Excel Keywords with Description column (Free text) match Standardization in Qliksense
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.
- Subscribe by Topic:
-
Chart
-
Data Load Editor
-
Developers
-
dimension
-
expression
-
filter
-
General Question
-
Script
-
Set Analysis
-
Variables
-
Visualization
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
