How to Pass Dimension value as Fieldname to an expression

[#1]

Hi,

• I have a data table in which the field '%FieldList' contains a list of fields names existing in my data model.

For example, if my datamodel has 3 fields Account, ClientID and City, the %FieldList will simply have 3 rows as following :

concat(%FieldList,',') = Account,ClientID,City

• To simplify my problem : I want to build a straight table with one row per fieldname and to use the name of the field in the current row as the fieldname parameter in a count() function into the expression.

• To sum it up : I want the text value of my dimension to be a parameter of the expression.

Let's say I have 2 accounts, 10 clientID and 3 cities.

I am trying to build a straight table such as :

- Dimension 'Field' : %FieldList

- Expression 'Count' : =count(DISTINCT %FieldList)

The result would be :

Field	Count
Account	2
ClientID	10
City	3

For now, the turnaround could be using a pick function with the list of each possibility (which could be dynamically written into a variable) as per below :

=pick(match(%_DimList,'Account','ClientID','City'),

  count(DISTINCT Account),

  count(DISTINCT ClientID),

  count(DISTINCT City))

But I wonder if there is a smarter & more efficient way to do that.

Thanks for your help !

Emilien

[sumitjadhav]

data:

LOAD Field,

     Count

FROM

[D:path);

load

Count,

if(match( Field, 'Account'),Count) as Account,

if(Match(Field,'ClientID'),Count) as ClientID,

if(Match(Field,'City'),Count) as City

Resident data;

Output: