Problem with Column of fields

[#1]

Hello everybody,

I have a problem which bothers me a lot. And I hope anyone of you can help me...

In my data model I have 3 different fields, called Col1, Col2 and Col3. Furthermore I have a field relevantCol which consists of the names of the three fields before. Now I would like to create an expression which returns the values of the columns mentioned in relevantCol.

I tried to use the expression

$(=relevantCol)

But this works only if one unique relevantCol has been chosen.

I think it will be clear if you have a look at the attached qvw. Just choose one item under relevantCol.

Is it possible to modify the expression in a way that it shows always the correct value?

Thanks a lot!

[#2]

I think you want:

GetFieldSelections(relevantCol)

[#3]

Thanks,

but I don't want the field names of the selected columns. I need the values of the listed columns under relevantCol:

NeededCol

a1

a2

b3

a4

b5

c6

[#4]

Sorry, I misunderstood.

I posted a sample. Is that what you're looking for? I had to use an if...then, I don't know if there is an easier way to do it. I also suppose that your real project is more complicated than the example.

[#5]

Unfortunately it is, indeed. The result is exactly what I am looking for. But I need it in a more abstract solution cause there will be more columns than 1 to 3. But it's not clear how much more, cause they can will be added dynamically.

I need something like lookup for charts. But unfortunately this function is only support in the script. But I don't want to implement this in the script (for other reasons...).

[rwunderlich]

If you can add a "Row" field to use as an index in your data table, you could use a FieldValue() function like this:

=FieldValue(relevantCol, Row)

Note the absence of quotes around relevantCol, causing the value of relevantCol to be used. If you have more than one row per dimension you will have to use aggregation expressions to get relevantCol and Row.

Example attached.

-Rob

[#7]

Dear Rob,

My experience is that FieldValue function works only if disctinct values are present in all the rows of the lookup value column. Even in your example, if you repeat any value in the Col1,Col2,Col3 the lookup value will not come correctly.

Please let me know if this understanding is correct.

Nimish

[rwunderlich]

Ouch. Nimish, you are correct. FieldValue holds the distinct values, not the number of rows. My example won't work.

-Rob

[#9]

Thanks,

I also thought already about FieldValue, which failed as you already mentioned because of the value which do not have to be distinct...

But maybe s.o. else has a solution?

[rwunderlich · Accepted solution]

This one drove my crazy. Seems there ought to be a way. If you could build in the script a variation of NMillers sugestion. An expression like:

=pick(match(relevantCol, 'Col1', 'Col2', 'Col3'), Col1, Col2, Col3)

I think that would do the trick. If you didn't want to do anything in the script, you could build it dynamically. Assign the following expression to a variable (vPickExpr):

='pick(match(relevantCol, ' & chr(39) & concat($Field,chr(39) & ',' & chr(39)) & chr(39) & '), ' & concat($Field, ',') & ')'

and then use

=$(vPickExpr)

as your chart expression. It includes all the Fields in the model, but gives the correct result given your sample data. Don't know how it would scale up.

Example attached including some columns not related to the chart.

-Rob