Getting attribute values for multiple fields from single attribute table

[#1]

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.

[#2]

hi! try this:

chart dimension -

if(CustomerCityID=AttributeValue,DisplayValue)

chart expression-

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

[cesaraccardi]

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

[#4 · Accepted solution]

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

[ramoncova06]

could you share data model or script ?

[#6]

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.