Pick(Match(ValueList

[jworthulf]

Customer Support,

I’m using Pick(Match(ValueList())) to populate my chart, it is working correctly on the first 2 expressions, but will not generate the numbers in the 3^rd expression.   

This is what the expression looks like

Pick(Match(ValueList($(exp_Dim)),$(exp_Dim)),
$(e_AdminRate),
$(e_BuildingRate),
$(e_DeliveryMilesRate),
$(e_DeliveryRouteRate),
$(e_Insurance),
$(e_Interest),
$(e_Marketing),
$(e_Multi-UnitRate),
$(e_Sales),
$(e_WarehouseRate))

I have tested the variable and the do calculate numbers correctly

Ex:

Please let me know what I need to do to resolve this issue.

[vinieme12]

can you post a screenshot of the expression editor

and also variable definition for exp_Dim

[marcus_sommer]

You may just query match() to see if all value-list entries will be resolved, for example:

Match(ValueList($(exp_Dim)),$(exp_Dim))

Beside of this you may consider to replace the valuelist() with native field-values created within an island-table, like:

dim: load dual(dim, recno()) as dim inline [
dim
a
b
];

and then your expression may look like:

pick(num(dim), expr1, expr2)

- Marcus

[jworthulf]

Marcus,

So I did a  querry match Match(ValueList($(exp_Dim)),$(exp_Dim))
//,
//$(e_AdminRate),
//$(e_BuildingRate),
//$(e_DeliveryMilesRate),
//$(e_DeliveryRouteRate),
//$(e_Insurance),
//$(e_Interest),
//$(e_Marketing),
//$(e_Multi-UnitRate),
//$(e_Sales),
//$(e_WarehouseRate))
//

And this is what my chart is showing which is what I would expect so when I add the pick function I should the variables should populate the rate field.     I checked my variables in a text object and they are calculating the correct numbers.

Is this possibly a version issue?   I created this sheet object using version 12.20 and then we updated to 12.60. 

[jworthulf]

Calculated Dim field

exp_Dim

'Admin',
'Building',
'Delivery_Miles',
'Delivery_Route',
'Insurance',
'Interest',
'Marketing',
'Multi-Unit',
'Sales',
'Warehouse'

[marcus_sommer]

I don't think that there is an issue with the releases. I could rather imagine that you have comments within your variables, like:

var:
sum(value) /* expression calculates ...

Applied as a single expression within a text-box it will work but integrated in a bigger expression it will cause problems because the entire variable-string including any comments will be taken and evaluated where it's called.

Should there be any comments - just remove them from the expression and put it into the variable comment-field.

- Marcus

[jworthulf]

So I went thru and tested each variable in the expression list and ran the pick(match( querry until I found the variable that was creating the issue.

It turns out the variable was $(e_Multi-UnitRate) when I replaced the variable w/ the expression it was supposed to execute it worked just fine.

If you know why the variable isn't working please let me know.    

[marcus_sommer · Accepted solution]

The expression for this variable looked ok. but the variable-name contains a special char. I never use them within variables - maybe this caused the issue. Therefore just try a replace from:

e_Multi-UnitRate

to

e_MultiUnitRate

- Marcus

[jworthulf]

Marcus,

Thanks for the help, getting rid of the special character took care of the issue!