Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Getting attribute values for multiple fields from single attribute table

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.

1 Solution

Accepted Solutions
Not applicable

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

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


5 Replies
Not applicable

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

hi! try this:

chart dimension -


if(CustomerCityID=AttributeValue,DisplayValue)

 

chart expression-


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



cesaraccardi
Valued Contributor

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

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

Not applicable

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

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
Valued Contributor III

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

could you share data model or script ?

Not applicable

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

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. 

Community Browser