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

[mrichman]

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";

[vvira1316]

Num() function is Qlik Function.

you may want to use

Load

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

    Data2;

SQL SELECT

    Data,

    Data2

FROM "MasterData";

[mrichman]

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]

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]

Try with :

Load

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

    Data2;

[mrichman]

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

[mrichman]

Hi Jean,

Removed them but still no success

[Anonymous · Accepted solution]

Load

    Num(Num#(Data)) as Data1,

    Data2;

SQL SELECT

    Data,

    Data2

FROM "MasterData";

[vvira1316]

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]

It works! Thanks for your help Robin.