when I understand you right, you read 20.000 times from an excel-file for mapping. This is really time-consuming. My suggestion is to load the map-table (lets call it MyMap) before the data table (REPORT 1)and then do the lookup against the resident table MyMap. After that use drop table MyMap; to get rid of it.
Welcome to QlikCommunity! If I understand your question correctly, you want to search inside a field and apply a map if the field contains a substring? There is a function in QV called substringcount(text,substring) that can probably help here. Looking at your Excel logic, I suspect it will be a little complicated in your case so I will leave it to you to write the actual code, but the general syntax would be something like:
applymap('mymap',mappingfield1) as Result
if(substringcount(field1,searchstring)>0,searchstring,null()) as mappingfield1
Thats correct , I'm looking to look inside a text feild to see if it contains a number from my mapping in column 1.
I have been trying the above, but for some reason I cant get my head around it !! ( maybe I need stronger coffee!!)
Can I run it by you to ensure I have an understanding ?
My Doc with text field I want to search is called REPORT , the Text field column is called OPICS.
The Table that I'm referencing is TABLE , the look up fields is KEY and the result is LABEL.
I have loaded mapping load of table as below
Label FROM [S:\Business Support\Qlikview\Preview\Table.xls]
Then loaded as normal my Report, - there are 2 other regular mapping on this and they're AOK - see below
LOAD Branch, [Deal No], [Rate Code], [Posting Date], Date, Currency, Amount, Rate, Basis, Opics, Code, Type, [Branch code], ApplyMap ('ratm', [Rate Code]) as [Structured or Unstructured], ApplyMap ('branchId',[Branch code]) as [Opics Team] FROM [S:\Business Support\Qlikview\Preview\Report loads\Master.xls]
Beneath this I have taken your suggestion ( I think !!) and have amended it to my data. I have tried a few different combinations but keep getting error messages on Load . I was trying to run map from 'numm' to look at Opics feild to cross reference this with the key and any matches come back as BO.
LOAD applymap('numm', Opics) as Result, if(substringcount(Opics,searchstring)>0,searchstring,null()) as BO FROM [S:\Business Support\Qlikview\Preview\Report loads\Master.xls]
Can you please help as its driving me crazy ?
Ah, I see. So you're not searching for 1 value but rather need to map to a different label depending on what key (if any) OPICS contains. This is not very simple, but Rob Wunderlich came up with a brilliant solution to exactly this problem. First, download his Cookbook here: http://robwunderlich.com/downloads/cookbook_V009.1.zip. Unzip, then open script\mappingWithWildcards.qvw. This application will describe how to achieve the functionality you're looking for.