Qlik Sense formatting numeric field as text

[Anonymous]

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

[jaimeaguilar]

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

[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.

[Anonymous · Accepted solution]

At the end, I deleted all charts where those fields were used and made them again. Now it works perfectly without using num#() or any other function

Thank you!