Qlik Community

Qlik Sense Data Connectivity

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

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

Tags (2)
5 Replies
Employee
Employee

Re: QlikSense SQL Numbers and Dates not Recognized

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

Re: QlikSense SQL Numbers and Dates not Recognized

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?

Employee
Employee

Re: QlikSense SQL Numbers and Dates not Recognized

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

Re: QlikSense SQL Numbers and Dates not Recognized

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!

Employee
Employee

Re: QlikSense SQL Numbers and Dates not Recognized

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

Community Browser