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

Wed, 12 Dec 2012 00:18:14 GMT

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!

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

nstefaniuk — Wed, 12 Dec 2012 07:34:29 GMT

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'
;

topic Re: Issue with a "vlookup" using SQL select inside of Qlikview in QlikView

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

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