Variable in expression

[vilstrup]

Hi guys,

i have a problem in a straight tabel where i would like a column that multiplies 3 fields.

TYPEOFSOURCING * PERIODTOREDESIGN * REVENUEIMPACT . I am currently stock.

Depending on what value the different fiels have i should get a number between 1-27.

if(TYPEOFSOURCING = 'Sole Source',3,

if(TYPEOFSOURCING = 'Single Source',2,

if(TYPEOFSOURCING = 'Dual Source',1,

if(PERIODTOREDESIGN = '> 12 months', 3,

if(PERIODTOREDESIGN = '3 to 12 months',2,

if(PERIODTOREDESIGN = '< 3 months',1,

if(REVENUEIMPACT = 'Accessories',3,

if(REVENUEIMPACT = 'ABL 90',2,

if(REVENUEIMPACT = 'ABL 800',2,

if(REVENUEIMPACT = 'AQT',2,

if(REVENUEIMPACT = 'TCM',1,

Can some one help me make this expression ?

Kind regards Nicolai

[Michiel_QV_Fan]

Creating the 1,2,3 values in your script is probably the better way to go.

[vilstrup]

Hi Michiel,

how would you do that in the script?

Kind regards Nicolai

[Michiel_QV_Fan]

hi,

you can run those if statements in the script and create new fields with it.

For instance:

Fact_table:

LOAD *,

if(TYPEOFSOURCING = 'Sole Source',3,

if(TYPEOFSOURCING = 'Single Source',2,

1))                                                                      as TYPEOFSOURCING_NR;

LOAD

     ...,

...

From etc.

Each row will get the value 3, 2 or 1 added in the new field.

Then in your expression: TYPEOFSOURCING_NR * PERIOD....NR * .......NR = 1-27

[marcus_sommer]

Similar to logic from Michiel but a bit optimized in performance and readability you could use:

match(TYPEOFSOURCING, 'Dual Source', 'Single Source', 'Sole Source') *

match(PERIODTOREDESIGN, '< 3 months', '3 to 12 months', '> 12 months') *

pick(match(REVENUEIMPACT, 'Accessories', 'ABL 90', 'ABL 800', 'AQT', 'TCM'), 3, 2, 2, 2, 1)

as MyScore

- Marcus