Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nynne1985
Contributor
Contributor

Mapping of strings/ mapsubstring/replace

Hi experts!

I’ve been trying to find a solution for this, but I only come across mapsubstring or mapping/replacements of single or few keywords, or where you want to remove part of a string.

I have a file with bank transactions with an explanation of the transaction in a free text field. Based on the nature of the transaction I’m mapping in some dimensions / classifications / categorizations. I find often times that the bank(s) or the transacting party incorporates different unique elements to the string so that classifying can get tideous because you’ve classified the same type of transaction before, but due to the unique element - I need to create a new mapping for this string.

Ok, so my thought here is to map the transaction based on a «replaced» version of the string, that would be easy to map going forward. Mapsubstring seems to be close, but I need the entire string to be replaced by the mapsubstring value. If there is a search hit on the string, the entire string should be replaced. Anyone has a solution for this? Mapsubstring is doing the trick really, but instead of just replacing the search-string with the substring value, I need to strip the string down to just having the substring value.

It should kind of be an applymap, but with a wildmatch/match/index approach to the search..

Sorry if I’m not able to explain this properly 😄


Mapsubstring table:

A,B

»Hello Kitty», «Kitty»

»Hospitalbill», «Hospital»

..

 

Transaction table:

String, NewField

»Invoice 1236 from Hello Kitty», «Kitty»

»Hospitalbill for August», «Hospital»

 

 

 

Labels (4)
1 Solution

Accepted Solutions
Saravanan_Desingh

One solution is.

Mapper:
Mapping
LOAD A, '@start@'&B&'@end@';
LOAD * INLINE [
    A, B
    Hello Kitty, Kitty
    Hospitalbill, Hospital
];

tab1:
LOAD *, TextBetween(MapSubString('Mapper',String),'@start@','@end@') As NewField;
LOAD * INLINE [
    String
    Invoice 1236 from Hello Kitty
    Hospitalbill for August
];

 

View solution in original post

4 Replies
Saravanan_Desingh

One solution is.

Mapper:
Mapping
LOAD A, '@start@'&B&'@end@';
LOAD * INLINE [
    A, B
    Hello Kitty, Kitty
    Hospitalbill, Hospital
];

tab1:
LOAD *, TextBetween(MapSubString('Mapper',String),'@start@','@end@') As NewField;
LOAD * INLINE [
    String
    Invoice 1236 from Hello Kitty
    Hospitalbill for August
];

 

Saravanan_Desingh

Output.

commQV65.PNG

nynne1985
Contributor
Contributor
Author

Thanks, it does look good!

However I was just about to log in to remove this post, because after I posted, I actually created some sort of solution myself, however a bit ugly - but might be good enough for me.. 

I created a table with an upper search, a lower string (only used internally, and because of the logic, has to be all normal letters), and the Final String.

nynne1985_0-1597520032908.png

Loaded as two mappings, and used applymap in combination with mapsubstring..

mapUpper: Mapping LOAD [UPPER STRING], [lower string] FROM table;
mapFinal: Mapping LOAD [lower string], [Final String] FROM table;

applymap('mapFinal', 
     trim(KeepChar(MapSubString('mapUpper', upper(text(String))), 'abcdefghijklmnopqrstuvwxyzæøå ')), text(String)) as SearchKey,

 This will eventually get the "Final String" replaced for the input-string if the search-string is found, and if there's no hits just keep the string as it is..

Thanks for your effort and help! If my "homecook" doesn't pan out, I'll give your solution a try 🙂

BigBadBoss
Contributor
Contributor

Thank you for this, it really helped me.

In my usecase I´m searcing for a textstring in  a list of URL:s. The textstring can either have the prefix 's=' or the suffix '-s' in the URL list. Can I adjust the formula to search for both in case there is no match in the first search?

I need to include Prefix / Suffix or I will get false positives.