topic Re: Variable in expression in QlikView

Variable in expression

vilstrup — Wed, 07 Nov 2018 09:53:21 GMT

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

Re: Variable in expression

Michiel_QV_Fan — Wed, 07 Nov 2018 10:00:25 GMT

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

Re: Variable in expression

vilstrup — Wed, 07 Nov 2018 10:04:13 GMT

Hi Michiel,

how would you do that in the script?

Kind regards Nicolai

Re: Variable in expression

Michiel_QV_Fan — Wed, 07 Nov 2018 11:25:47 GMT

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

Re: Variable in expression

marcus_sommer — Wed, 07 Nov 2018 11:37:58 GMT

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