topic Re: Getting attribute values for multiple fields from single attribute table in QlikView

Getting attribute values for multiple fields from single attribute table

Thu, 21 May 2015 11:41:29 GMT

Hi all,

I have a Lead table with fields CustomerName, CustomerCityID, LeadStageID, LeadStageStatus. Other than CustomerName, all other fields have numeric values which map to a corresponding table with 3 fields: AttributeName, AttributeValue and DisplayValue where AttributeName has values similar to the fieldName (CustomerCityID, LeadStageID, etc).

So I want to do an analysis of # of customers by City, I need to map Lead.CustomerCityID to Attribute.AttributeDisplayValue where AttributeName='CityID' and AttributeValue=<the value in CustomerCityID>.

How do I make this connection for either a straight table or for a bar graph? What would my dimensions and expressions be for a bar graph which shows count of customers by City name?

Thanks for your help.

Re: Getting attribute values for multiple fields from single attribute table

Thu, 21 May 2015 13:16:15 GMT

hi! try this:

chart dimension -

if(CustomerCityID=AttributeValue,DisplayValue)

chart expression-

count({<AttributeName={'CustomerCityID'}>}CustomerName)

Re: Getting attribute values for multiple fields from single attribute table

cesaraccardi — Thu, 21 May 2015 13:38:34 GMT

Hi Jaideep,

I suggest you first prepare the data model using mapping tables, for example:

MAP_CITY:
MAPPING LOAD

AttributeValue,

DisplayValue

FROM

MAP_TABLE

WHERE AttributeName = 'CityID;

MAP_STAGE:
MAPPING LOAD

AttributeValue,

DisplayValue

FROM

MAP_TABLE

WHERE AttributeName = 'LeadStageID';

(etc...)

LEAD:

LOAD

CustomerName,

ApplyMap('MAP_CITY',CustomerCityID) as CustomerCity,

ApplyMap('MAP_STAGE',LeadStageID) as LeadStage,

ApplyMap('MAP_STAGE_STATUS',LeadStageStatus) as LeadStageStatus

FROM LEAD_TABLE;

//---------------------------------

Then you can just add CustomerCity as a dimension and create a count function for the expression.

Hope this helps,

Cesar

Re: Getting attribute values for multiple fields from single attribute table

Thu, 21 May 2015 13:57:56 GMT

If you are willing to change your data model then you could simply load multiple attribute table for each field..so for

Cities something like below which will be linked to Lead table by CustomerCityID

CustomerCity:
Load AttributeValue as CustomerCityID,
DisplayValue as City
Resident Attribute
where AttributeName='CustomerCityID';

Re: Getting attribute values for multiple fields from single attribute table

ramoncova06 — Thu, 21 May 2015 15:53:18 GMT

could you share data model or script ?

Re: Getting attribute values for multiple fields from single attribute table

Thu, 28 May 2015 18:38:37 GMT

Thanks all for your responses. I used the 3rd option of creating multiple attribute tables since my data was being obtained through an SQL load which did not allow me to use the ApplyMap function.