Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

drportillo
New Contributor

Qlik Sense formatting numeric field as text

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

1 Solution

Accepted Solutions
drportillo
New Contributor

Re: Qlik Sense formatting numeric field as text

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!

3 Replies
jaimeaguilar
Valued Contributor II

Re: Qlik Sense formatting numeric field as text

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
New Contributor III

Re: Qlik Sense formatting numeric field as text

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.

drportillo
New Contributor

Re: Qlik Sense formatting numeric field as text

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!

Community Browser