Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mrichman
New Contributor III

How to use Qlik num() or num#() function, in SQL Select properly?

Hi Community,

I have been trying to use number format one of my data field, however I'm getting an error on this the whole time. Would be nice if you could help, thanks in advance!

Error:

SQL##f - SqlState: 37000, ErrorCode: 4294964196, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Num(["Data"],'############')'.

Script Example:

SQL SELECT

    Num(["Data"],'############') as Data1,

    Data2

FROM "MasterData";

1 Solution

Accepted Solutions
roharoha
Valued Contributor III

Re: How to use Qlik num() or num#() function, in SQL Select properly?

Load

    Num(Num#(Data)) as Data1,

    Data2;

SQL SELECT

    Data,

    Data2

FROM "MasterData";

12 Replies
vvira1316
Valued Contributor II

Re: How to use num() or num#() function SQL Select properly?

Num() function is Qlik Function.

you may want to use

Load

    Num(["Data"],'############') as Data1,

    Data2;

SQL SELECT

    Data,

    Data2

FROM "MasterData";

mrichman
New Contributor III

Re: How to use num() or num#() function SQL Select properly?

Hi Vijay!

Thanks for your prompt reply. Unfortunately it is not working. I'm getting this error: Field not found - <"Data">.

Do you have any suggestions further? 

mikaelsc
Contributor III

Re: How to use num() or num#() function SQL Select properly?

why the square brackets in your sql select and in your load statement?

it's (usually) either the one or the other "" or [], not both..

sergio0592
Valued Contributor II

Re: How to use Qlik num() or num#() function, in SQL Select properly?

Try with :

Load

    Num(Data,'############') as Data1,

    Data2;

mrichman
New Contributor III

Re: How to use Qlik num() or num#() function, in SQL Select properly?

You are right, I removed tried both one by one but it still doesn't work

mrichman
New Contributor III

Re: How to use Qlik num() or num#() function, in SQL Select properly?

Hi Jean,

Removed them but still no success

roharoha
Valued Contributor III

Re: How to use Qlik num() or num#() function, in SQL Select properly?

Load

    Num(Num#(Data)) as Data1,

    Data2;

SQL SELECT

    Data,

    Data2

FROM "MasterData";

vvira1316
Valued Contributor II

Re: How to use Qlik num() or num#() function, in SQL Select properly?

What is not working? Can you please share sample data file. I hope you have data point that has name "data" in the source MasterData.

mrichman
New Contributor III

Re: How to use Qlik num() or num#() function, in SQL Select properly?

It works! Thanks for your help Robin.

Community Browser