Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.


Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Wildcards in Lookup

I am trying to re-create what Lookup is doing in Excel.

The data is an extract of journal entries from our accounting system. One of the fields, Description, contains multiple data elements, one of which is the company name. (To make matters worse, the field is free-form and our sales team doesn't enter the data in a consistent format.)

In order to parse the data, the previous developer wrote a LOOKUP that also uses a MATCH with wildcards to scan the Description field for the company name in column A in another tab, and then get the corporate alias that corresponds from column B of the array. (If there is no match, it defaults to OTHER.) This is the Excel formula:

=IFERROR(LOOKUP(10^99,MATCH("*"&'Corp Account Lookup'!$A$2:$A$900&"*",Q439,0)/('Corp Account Lookup'!$A$2:$A$900<>""),'Corp Account Lookup'!$B$2:$B$900),"OTHER")

I've been trying to replicate it for two days but having no luck at all. The closest I came was doing a WildMatch of the known company names to the description - it worked, but it also resulted in multiple hits because the patterns exists multiple times.

Anyone have any suggestions?