topic Re: using the Fieldname to retrieve fieldvalue in QlikView

using the Fieldname to retrieve fieldvalue

Fri, 15 Jul 2011 16:24:43 GMT

I have a fields named 'A' untill 'Z'. In my chart the dimension is $FieldNo and I want one expression so that when $FieldNo = 1 -> sum(A) ; $FieldNo=2 -> sum(B), etc. I tried making an expression while loading, being 'sum(A)', etc, but that works just for one single value.

I want it to work what in Excel the function INDIRECT() does!

using the Fieldname to retrieve fieldvalue

patrickanderson — Fri, 15 Jul 2011 17:47:49 GMT

=FieldValue($Field,1)

=sum(FieldValue($Field,1))

I am having trouble understanding the exact result you needed but this might help?

Re: using the Fieldname to retrieve fieldvalue

rwunderlich — Fri, 15 Jul 2011 20:04:15 GMT

=sum(pick($FieldNo,A, B, C))

or more generalized

=sum(pick($FieldNo, $(vFieldList)))

the second option requires you to define the vFieldList variable as follows:

SET vFieldList = =concat({1} $Field, ',');

Note two equal signs. The = must be part of the variable def.

-Rob

http://robwunderlich.com

Re: using the Fieldname to retrieve fieldvalue

Sat, 16 Jul 2011 20:37:41 GMT

A little explanation might help:

I load about 50 KPI's from an Excel-format for every location and every week. Then I want to represent for a selected week the data as follows: dimension 'location' in the x-direction of the table and the KPI's in the y-direction. To prevent that I have to write a lot of if-statements to get the KPI's in the y-direction, I was trying to use $Field and/or $FieldNo as dimension and just one Sum-statement which is based on $Field or $FieldNo to determine what field should be calculated. I thought using the value of $Field as the fieldname in sum(). But maybe there is some other way to achieve this?

I hope I made it more clear.

Liesbeth