topic FieldValue function not working as hoped in QlikView

FieldValue function not working as hoped

avastani — Wed, 12 May 2010 16:35:53 GMT

Has anyone been able to solve this mystery in their QV Adventures?

Essentially, I store my KPI and their definition in a table and then use it in a pivot table as below, but the FieldValue function only returns a string and does not evaluate the expression definition as you can see. I wrap it around a $(FieldValue...()) and it does not work either.

Any suggestions are most appreciated.

FieldValue function not working as hoped

Wed, 12 May 2010 18:06:43 GMT

Hello Amir, I have had the same problem. For my experience I can tell you that the dollar sign expasion is evaluated only once, so if you have multiple KPI's it wont work, however if you select only one, the value should be displayed. What I did to solve the problem is loading the KPI as you did with an ID column, which was a plain rownno() function. When yo create your chart, use the pick(ID, expr1, expr2,...) as the expression. Notice that you can use your dollar expansion within the values of the pick function. This approach has a drawback, since you would have to place an expr within the pick function for every KPI. If you want to add KPI without the need to edit your char expression, you can generate the pick strucuture within the script, based on the number of KPI you are loading.

Hope this helps.

Regards

FieldValue function not working as hoped

avastani — Wed, 12 May 2010 18:55:56 GMT

Dear Ivan,

That's an excellent suggestion. I tried it out. It works like a charm! Flawless. Thanks much!!

I wonder if I could do a pick(rowno(), $([MyExpressionList])) where my MyExpressionList is field or combine it with a Dual() and FieldValue to get a more optimal solution where I don't have to spell out the list because if I have 20 KPIs and I want their variances as well, I don't want to type it all out.

FieldValue function not working as hoped

Wed, 12 May 2010 19:09:09 GMT

Hi Amir, I rembered trying to implent something very similar to the approach you suggested, what I did is concatenate all you KPI's formulas as you load them into the data cload:

Load ID,

expr,

previous(exprconcat) & if(rowno() = 1, '', ',') & expr as exprconcat

from a;

I can't guarantee the syntax, but thats the idea. Once you do that, you would have all your expression concatenated in the last record, so your expression might look something like these:

pick(ID, $(vExprConcatLastRecord) )

Regards