6 Replies Latest reply: Apr 13, 2018 5:03 AM by Jonathan Dienst RSS

    Round() help

    Mahitha M
    Mahitha M Apr 13, 2018 2:59 AM

      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.

      • 124 Views
      • Tags:
        • Re: Round() help
          Petter Skjolden
          Petter Skjolden Apr 13, 2018 3:44 AM (in response to Mahitha M)

          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' )

            • Re: Round() help
              Mahitha M
              Mahitha M Apr 13, 2018 4:26 AM (in response to Petter Skjolden )

              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 )

                • Re: Round() help
                  Petter Skjolden
                  Petter Skjolden Apr 13, 2018 4:31 AM (in response to Mahitha M)

                  || 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 )



                    • Re: Round() help
                      Mahitha M
                      Mahitha M Apr 13, 2018 4:48 AM (in response to Petter Skjolden )

                      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.

                • Re: Round() help
                  Jonathan Dienst
                  Jonathan Dienst Apr 13, 2018 4:59 AM (in response to Mahitha M)

                  I think Petter has covered this perfectly