Issue with a "vlookup" using SQL select inside of Qlikview

[#1]

Hello!

I will try my best to explain my issue and hopefully you smart community of developers can assist me

I am loading a "lookup" table from an excel document as its own table in Qlikview, then subsequently using a SQL select statement to grab data into another named table with the goal to later reference the table to return GeneratorCategory's value.

So,

//Lookup Table

LeadGenRole:

LOAD Lead_Generator_Role,

          MAX_LEAD_ROLE,

          GeneratorCategory

FROM

               (ooxml, embedded labels, table is Generator);

And then,

//SQL data

Bookings:

Select
          om.*
          ,sf2.MAX_LEAD_ROLE

,CASE

     when om.CATEGORY in ('Partner','Shared') then om.CATEGORY

     else (If the condition fails, then lookup sf2.MAX_LEAD_ROLE against LeadGenRole's MAX_LEAD_ROLE and return GeneratorCategory)

          end as FinalCategory

from [Server].[dbo].[View] om

left join [Server2].[DB].[dbo].

sf2

      on om.Opportunity_ID = sf2.OPPORTUNITY_ID


where om.DATE >= '201202'
;

What I am then trying to do is perform a CASE statement (or qlikview equivalent) to lookup the MAX_LEAD_ROLE field on the excel document (LeadGenRole table) and return the GeneratorCategory value.  I am uncertain on how to correctly write the syntax to reference the GeneratorCategory field on the LeadGenRole table inside of the SQL statement

Help is VERY appreciated!

[nstefaniuk]

Hello.

You can use Mapping Load and ApplyMap:

//Lookup Table

LeadGenRole:

Mapping LOAD

          MAX_LEAD_ROLE,

          GeneratorCategory

FROM

               (ooxml, embedded labels, table is Generator);

//SQL data

Bookings:

Load

*,

if(CATEGORY = 'Partner' or CATEGORY = 'Shared', CATEGORY,  ApplyMap('LeadGenRole', MAX_LEAD_ROLE, 'Not found') ) As FinalCategory

;

Select
          om.*
          ,sf2.MAX_LEAD_ROLE

,om.CATEGORY

from [Server].[dbo].[View] om

left join [Server2].[DB].[dbo].

sf2

      on om.Opportunity_ID = sf2.OPPORTUNITY_ID


where om.DATE >= '201202'
;