Dynamic Pick and Match

[Anonymous]

Hi!

We have this code:

Pick(Match(IDArbol, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28)

LOAD *,

     Pick(Match(IDArbol, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28),

$(vVARIABLE_1),

$(vVARIABLE_2),

$(vVARIABLE_3),

$(vVARIABLE_4),

$(vVARIABLE_5),

$(vVARIABLE_6),

$(vVARIABLE_7),

$(vVARIABLE_8),

$(vVARIABLE_2),

$(vVARIABLE_9),

$(vVARIABLE_10),

$(vVARIABLE_1),

$(vVARIABLE_12),

....

          $(vVARIABLE_28)

Unfortunately the number of IDArbol could go up from 28, is there any way we could do a dynamic Pick and Match to solve this or another function which could work similar to pick and match?

Thank you!

[marcus_sommer]

I think you don't want to hear the following but suggest to rethink the whole approach.

Your variables seems to be rather unique so that it makes not much sense to put these contents within variables. Further the content isn't really dynamic - nearly all values within the conditions and the returns are hard-coded and I think it are far too many nested branches as that it is sensible to develop such huge constructs - from a maintainability point of view it looked rather like a nightmare ...

I think I would go in the direction of creating one or several matrix-tables (maybe filled from an Excel source) which are associated or merged (joined and/or mapped) to your table to simplify the matching of the right forecast-factors.

- Marcus