Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
SK28
Creator II

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.

 

1 Solution

Accepted Solutions
Digvijay_Singh

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;

Digvijay_Singh_0-1660876955608.png

 

 

View solution in original post

2 Replies
chaorenzhu
Creator II

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

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;

Digvijay_Singh_0-1660876955608.png