3 Replies Latest reply: Nov 2, 2016 12:25 PM by Daniel R P RSS

    Qlik Sense formatting numeric field as text

    Daniel R P

      I'm loading data to Qlik Sense 3.1 from SQL Server. There are some fields defined as decimal in SQL Sever but when I load them from SQL, they appear as text or URL, so I can't show totals for those fields in a chart. I've tried using num() and num#() functions and there's no change.

       

      [Stock] and [Minimo] are defined as decimal(38,20) in SQL, and should be numeric in Qlik Sense

      [puntos] and [ObjetivoLiquidar] are defined as varchar(10), but num# function doesn't work and it is also shown as text or URL

       

      This is my data load script for this table:

       

      ProductosaLiquidar:    

           UNQUALIFY *;    

           LOAD ID as ID_LIQ,        

                Trimestre,        

                FechaInicio,        

                FechaFin,        

                Cod as  [Articulo ID],        

                Stock AS [Stock Liquidacion],        

                Minimo as MinimoLiq,        

                Num#(Trim(ObjetivoLiquidar)) as ObjetivoLiquidar,        

                num#(puntos,'#,#') as PuntosLiq;    

           SQL SELECT ID,        

                Trimestre,        

                FechaInicio,

                FechaFin,

                Cod,        

                Stock,       

                Minimo ,        

                ObjetivoLiquidar,        

                puntos    

           

      FROM database.[dbo].ObjetivoLiq;

       

      I have tried num() and num#() with all of them and I don't know if I'm missing something or how should I do it. Anyone can help?

       

      Thanks,

      Daniel

        • Re: Qlik Sense formatting numeric field as text
          Jaime Aguilar

          Hi,

          could please upload a screenshot of the results you're getting in QlikView? I think this way it could be easier to understand what's happening and help you.

           

          If you run this in QlikView:

               SQL SELECT ID,       

                    Trimestre,       

                    FechaInicio,

                    FechaFin,

                    Cod,       

                    Stock,      

                    Minimo ,       

                    ObjetivoLiquidar,       

                    puntos   

                FROM database.[dbo].ObjetivoLiq


          what is the result you get?


          regards

          • Re: Qlik Sense formatting numeric field as text
            Elva Lester

            Have you tried using a SQL Convert function in the SQL Select statement?

             

                 SQL SELECT ID,       

                      Trimestre,       

                      FechaInicio,

                      FechaFin,

                      Cod,       

                      CONVERT(DECIMAL(38,20),Stock) AS Stock,      

                      CONVERT(DECIMAL(38,20),Minimo) AS Minimo,       

                      ObjetivoLiquidar,       

                      puntos   

                

                 FROM database.[dbo].ObjetivoLiq;

             

            I hope this helps.