Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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
There was a pasting error on the mapping load -- it should look like this
Mapping
LOAD Key,Label
FROM
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,
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