Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Creating the 1,2,3 values in your script is probably the better way to go.
Hi Michiel,
how would you do that in the script?
Kind regards Nicolai
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
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