Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QlikSense SQL Numbers and Dates not Recognized

I am pulling data into QlikSense from SQL Server.  The data is loading fine, but QlikSense doesn't seem to recognize the numbers as numbers.  I can get dates to format correctly by creating a new dimension and wrapping the field with Date ([Field Name]).  However I cannot get numbers to be recognized as numbers.  The SQL data is defined as decimal(15,2).  Even the data preview shows values (see attached) but graphs and any aggregation returns a zero for everything.

Has anyone had this issue before and resolved it?

Thanks!

Tom

6 Replies
JonnyPoole
Employee
Employee

Hi - are you using any chart aggregation functions to total these numbers correctly ? 

Try:

sum( [Valuation] )

...for example as the measure.  There is also count() , min(), max(0 etc etc.. etc..

Should work but say if it doesn't and we'll try something else.

Not applicable
Author

I've tried sum( [Valuation] ), and sum( Money ([Valuation]) )  - both return zeros.  I also tried just adding the Valuation column to a table and they also come back with all "-".

What else can I try?

JonnyPoole
Employee
Employee

Try going to the load editor and where you see Valuation being loaded, wrap it with the num() function with an alias and then reload the data. 

IE:

Load

     *,

     num(Valuation) as ValuationAsNum;

SQL .....

This will load a 2nd field called 'ValuationAsNum' . Please try that...

---------------------------------

if that fails, i wonder if you can save the SQL results to CSV and replicate the same problem loading from a CSV ? If you can , please post the CSV so i can try too.

Not applicable
Author

That didn't work because "Microsoft SQL Server Native Client 11.0, ErrorMsg: 'Num" is not a recognized built in function name.

For some odd reason, my Data connections panel has also disappeared. So  - I tried creating a new app all together with the same data script and the problem seems to have gone away. Numbers are now recognized in the new app.

Thank you for your help!

JonnyPoole
Employee
Employee

Great its working. it sounded weird. 

PS:  num() is a qlik sense function and wouldn't work within SQL. You would have to use it in the LOAD (which is optional but recommended) piece of script that goes preceding the SQL in the load editor.  It gives you access to non-sql transformations in qlik and more...

michaelfreitas
Creator
Creator

5 years later and I had the same problem.

I only solved when:

1. I created an ODBC connection on windows

2. I created a connection in Sense pointing to this one.

 

SQL SERVER

QLIK SENSE SERVER FEBRUARY 2019 PATCH 1 (13.9.2)