Define a Valuelist variable dimension from another Valuelist variable dimension

[davdiste]

Hi all,

I'm  trying to create TABLE based on 2  variable, such as the following:

V_1:
ValueList(
'APPLE','ORANGE','LEMON',
'WATER','COLA','SODA',
'SALAD','CARROTS')

V_1AGGR:
ValueList(
if($(V_1) = 'APPLE', 'FRUIT',
if($(V_1) = 'ORANGE', 'FRUIT',
if($(V_1) = 'LEMON', 'FRUIT',
if($(V_1) = 'WATER', 'DRINK',
if($(V_1) = 'COLA', 'DRINK',
if($(V_1) = 'SODA', 'DRINK',
if($(V_1) = 'SALAD', 'VEG',
if($(V_1) = 'CARROTS', 'VEG')))))))))

the V_1AGGR  aggregate the products in larger catergories.

The problem is that the variable V_1AGGR doesn't work, the table display the symbol - in return.

Anyone knows if it's possible to define, with variables, a Valuelist based on another Valuelist like the example posted above?

Thanks
Davide

[sunny_talwar]

If you plan on using V_1AGGR as an expression... this should work

Pick(Match($(V_1), 'APPLE', 'ORANGE', 'LEMON', 'WATER', 'COLA', 'SODA', 'SALAD', 'CARROTS'), 'FRUIT', 'FRUIT', 'FRUIT', 'DRINK', 'DRINK', 'DRINK', 'VEG', 'VEG')

For dimension, I would suggest creating Island table in the script

[davdiste]

Thx Sunny,

Yes I need that variable as dimension (as expression works, but is not usable on chart e.g.).

For dimension, I'll try to create the island table in the script and then I'll apply the same expression you suggested in the answer.

[sunny_talwar]

You won't need an expression if you do it in script like this

LOAD * INLINE [
    V_1, V_1AGGR
    APPLE, FRUIT
    ORANGE, FRUIT
    LEMON, FRUIT
    WATER, DRINK
    COLA, DRINK
    SODA, DRINK
    SALAD, VEG
    CARROTS, VEG
];

now use V_1 and V_1AGGR as your dimension