Function to working with text

[Anonymous]

Hi,

Maybe someone knows solution for my issue.

I want to create function for working with text like 'mapsubstring' but it won't match searched string when it is is part of word of mapping string. So far I created the function with 2 parameters: textfunction(search string, mapping string) and it work well but only with single string. I don't know how to add in this function a mapping range (like in the mapsubstring or applymap functions) and logic to work with it (maybe somehow with For...each).

Could you help me with it?

Thanks, Alex.

[Gysbert_Wassenaar]

Can you give some examples? Better yet, post a small qlikview document that illustrates the problem.

[Anonymous]

Hi Gysbert,

sure I can:

Now JavaScript function looks following:

function Mapwholeword(string, occurrence,caseSensitive) {

    var regexes = [new RegExp(".*\\W" + occurrence + "\\W.*"),
         new RegExp(".*\\W" + occurrence + "$"),
         new RegExp("^" + occurrence + "\\W.*"),
         new RegExp("^" + occurrence + "$")
     ];

     if (!caseSensitive) {
         string = string.toLowerCase();
         occurrence = occurrence.toLowerCase();
     }
     for (var i = 0; i < regexes.length; i++) {
         var match = regexes.exec(string);
         if (match !== null) {
             return occurrence;
         }
     }
     return false;
}
//string - string in which we seek compliance
//occurrence - string from mapping file
//caseSensitive if true, caseinSensetive if false

Examples: Mapwholeword('Finance and tax ', 'Finance', false) return Finance

                  Mapwholeword('Finance and taxation ', 'Finance and tax', false) return false or null;

But I want use instead  'occurrence' parameter the range with 2 columns.

For example

Convert table:

Search    Convert to

Finance    Finance sphere

Taxation   Finance an tax sphere

ect.

and I want to write smh like that Mapwholeword('Finance and tax ', Convert table , false) return Finance sphere

ask more if you not understand

Thanks, Alex

[Gysbert_Wassenaar]

Ok, I think I understand now. Unfortunately I don't see a way to achieve what (I think) you want. Qlikview isn't really equiped for that kind of text processing. Perhaps somebody else knows how to do this. rbecher‌, any ideas?

[rbecher · Accepted solution]

Do not understand all cases but why not using MapSubstring with delimiters, something like that:

MapCheckWholeWord:
Mapping LOAD * inline [
x,y
Finance,<found>
Finance#and#tax,<found>];

MapWholeWord:
Mapping LOAD * inline [
x,y
Finance,<Finance>
Finance#and#tax,<Finance and tax>];

Result:
LOAD ...
If(Index(MapSubstring('MapCheckWholeWord', '#' & Replace(Field, ' ', '#') & '#'), '<found>') > 0,
  TextBetween(MapSubstring('MapWholeWord', '#' & Replace(Field, ' ', '#') & '#'), '<', '>'),
  '..not found') as mapped

[tresesco]

Not really sure if I understood it right. May be the idea I showed here could help Re: join two tables by substring

[Anonymous]

Hi Ralph,

thank you for your answer,

It' s good solution, I only added '#' at the beginning and at the and of words otherwise script take a part of mapping word(for example it mapped 'air' in the 'chair' or 'tax' in the 'taxation'):

MapCheckWholeWord: 
Mapping LOAD * inline [ 
x,y 
#Finance#,<found> 
#Finance#and#tax#,<found>]; 
  
MapWholeWord: 
Mapping LOAD * inline [ 
x,y 
#Finance#,<Finance> 
#Finance#and#tax#,<Finance and tax>]; 

Best regards, Alex