Mapping Load

[#1]

Hi -- This is my First post !!!

I have read the posts above and they dont quite answer my question.

I'm loading an excel doc REPORT 1 into QV, one of the columns , called DATA , has text and numbers. I have a second table in excel , where using and Index formula it searches within the text field for a specific number and if its found returns a value predetermined in the table.

This is the Excel formula

IF(A2="","",LOOKUP(9.99E+307,SEARCH(INDEX(REPT("ZZZZ",Table!$B$1:$B$201="")&Table!$B$1:$B$201,0),TEXT(A2,"00000")),Table!$C$1:$C$201)),A2)

The table looks a little like this

Column A Column B

41500002 Green

41500003 Blue

The REPORT 1 doc is about 20,000 rows long and the table is about 10,000 rows, so,the index formula is time consuming to run.

I was wondering if there was a "Partial map" function in QV, where we would load REPORT 1 and map the Table to DATA column , IF it CONTAINS Column A.

Any suggestions would be great

Thanks

Anne

[#2]

Hello Anne,

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.

HtH, Roland

[vgutkovsky]

Anne,

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:

LOAD
applymap('mymap',mappingfield1) as Result
;
LOAD
if(substringcount(field1,searchstring)>0,searchstring,null()) as mappingfield1
RESIDENT mydata;

[#4]

Hi Vlad,

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

Mapping LOAD Key, LOAD Key, LOAD Key, Key,

Label

FROM

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

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

Can you please help as its driving me crazy ?

Thanks

Anne

[#5]

There was a pasting error on the mapping load -- it should look like this

Mapping

LOAD Key,

Label

FROM

[vgutkovsky]

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.

Regards,

[#7]

Hi Vlad,

Thanks very much , I found that file earlier and am reading my way through it now.

It actually makes sense !!!!

Thanks again for your help ,

Anne