topic Re: Expression for column in QlikView

Expression for column

marco_puccetti — Wed, 22 Apr 2015 13:27:24 GMT

I need to add a more complex script for the computation of a delta percentage for two year in a pivot table.

The script should contains the following lines of codes but the syntax checker doesn't allow me to insert the code.

How can i do?

This is the code:

Column(1): Current year field

Column(2): Previous year field

if(Column(1) > Column(2)) then

(Column(1)-Column(2)) / Column(1);

end if

if(Column(1) <= Column(2)) then

((Column(2)-Column(1)) / Column(2)) * (-1);

end if

Thanks

Re: Expression for column

datanibbler — Wed, 22 Apr 2015 13:33:20 GMT

Hi Marco,

IF_THEN is a conditional function in QlikView for entire statements or blocks of code - you cannot use it in an expression.

Try like this:

IF(Column(1) > Column(2)),

(Column(1)-Column(2)) / Column(1),

if(Column(1) <= Column(2)),

((Column(2)-Column(1)) / Column(2)) * (-1)))

I'm not quite sure about the number of closing brackets at the end, but the expression_editor will tell you.

HTH

Re: Expression for column

Wed, 22 Apr 2015 13:37:55 GMT

Hi,

(

Column(1) > Column(2),

(Column(1)-Column(2)) / Column(1),

(Column(2)-Column(1)) / Column(2) * (-1)

)

Xavier.

Re: Expression for column

marco_puccetti — Wed, 22 Apr 2015 13:52:27 GMT

Ok with this sintax it works, but if i need a more complex expression that has to be written on more lines how can i do?

Thanks

Marco

Re: Expression for column

Wed, 22 Apr 2015 14:03:09 GMT

The syntax is :

If (condition, expression if true, expression if false)

=> you can imbricate more IF statements into the "expression if false".

Example:

If ( a=1, 'a',If (a=2, 'b',If (a=3, 'c',If (a=4, 'd', 'z'))))

If ( a=1, 'a',

If (a=2, 'b',

If (a=3, 'c',

If (a=4, 'd', 'z'))))

is equivalent to :

if a=1 then

'a'

elseif a=2 then

'b'

elseif a=3 then

'c'

elseif a=4 then

'd'

else

'z'

end if

Xavier.