Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Highlighted
jlahners
New Contributor

Identify all unique substrings

Hi all -

I'm trying to build a view that will show me all the unique words used a field in my product description and am having some challenges.  I'm starting by removing all the special characters found in my data (of which I can get most out but not all) and second I'm trying to remove a few "known words" from my list because I don't want to report on common words. 

Here's an example:

 

Summaries:
MAPPING LOAD * INLINE [
Summary
This is for a desktop deployment
This is for a new switch to replace ABC-123
"Need to install a new mouse"
Running cable for John & Mike
];

WordsToRemove:
LOAD * INLINE [
BadWords
for
the
need
this
];

InitialWords:
LOAD lower(SubField(MapSubString('SpecialChars',[Summary]),' ')) as Word RESIDENT [Summaries];

ValidWords:
LOAD Word AS NewWord
RESIDENT InitialWords 
WHERE LEN(Word)>3 
AND NOT Exists(BadWords,Word);

Problems:

  1. I can't find a way to remove some special characters, especially the Microsoft "long dash" and their fancy quote symbols, as well as the ']' (because it breaks the inline table load)
  2. The remove of the "bad words" isn't working at all - they still show up in my resultant list

Any help would be much appreciated!  Thank you in advance! 

4 Replies

Re: Identify all unique substrings

Mapping table require 2 field while you are mapping.

1. May be use purgechar() function
2. This will done if you plug corrrct way of syntax.
jlahners
New Contributor

Re: Identify all unique substrings

How do I type the Microsoft quotes and long dash in the load editor? As for the Not Exists, I must be missing the syntax error. Can you expand?
jlahners
New Contributor

Re: Identify all unique substrings

Thank you for the pointers, I was able to get it fixed up.  My biggest problem was I needed a TRIM() call in the NOT EXISTS section.  

vkish16161
Contributor III

Re: Identify all unique substrings

Try like this:

Load * inline "

";

Community Browser