Round() help

[mahitham]

Hi Experts,

Can any one please help me to convert below oracle decode function into Qlik UI as expression.

round( Sum ( decode ( [name] , 'valuerisk', [Value] ,0 ) ) /1000, 2 )

Here name and Value are the fields.

jontydkpi  could you please help me.

Thnaks in advance.

[petter · Accepted solution]

I think this is what you are looking for:

Round( Sum( If( [name] = 'valuerisk' , [Value] , 0 ) ) / 1000 , 0.01 )

You might have to add Num for the display:

Num( Round( Sum( If( [name] = 'valuerisk' , [Value] , 0 ) ) / 1000 , 0.01 ) , '0.00' )

[mahitham]

Hi Petter,

Thanks a lot for your reply. Its working fine. Some expression contains like below.

Could you please help me to convert this || 'Y' as well. Cant create new thread again

round( Sum ( decode ( [name] , 'valuerisk' || 'Y', [Value] ,0 ) ) /1000, 2 )

[petter]

|| is Oracle string concatenation operator for joining two strings together.

So it would be the same as:

Round( Sum( If( [name] = 'valuerisk' || 'Y' , [Value] , 0 ) ) / 1000 , 0.01 )

That should be exactly the same as:

Round( Sum( If( [name] = 'valueriskY' , [Value] , 0 ) ) / 1000 , 0.01 )

[mahitham]

Thanks petter

Num( Round( Sum( If( [name] = 'valuerisk' , [Value] , 0 ) ) / 1000 , 0.01 ) , '0.00' ) THIS EXPRESSION is working fine in UI

but when I have moved the same expression to backend script its showing invalid expression. Actually to apply group by logic I have to move to script only there is 50 expressions like this.

could you please help me on this.

[jonathandienst]

I think Petter has covered this perfectly

[jonathandienst]

In SQL (and in Qlik), when you include an aggregation function like Sum(), all other fields not in some aggregation need to be in the Group By. That is how it is.