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

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

mrichman — Wed, 25 Nov 2020 16:16:04 GMT

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

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

vvira1316 — Wed, 16 May 2018 18:58:55 GMT

Num() function is Qlik Function.

you may want to use

Load

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

Data2;

SQL SELECT

Data,

Data2

FROM "MasterData";

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

mrichman — Wed, 16 May 2018 19:10:21 GMT

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?

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

mikaelsc — Wed, 16 May 2018 19:28:43 GMT

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

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

sergio0592 — Wed, 16 May 2018 19:30:24 GMT

Try with :

Load

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

Data2;

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

mrichman — Wed, 16 May 2018 19:39:43 GMT

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

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

mrichman — Wed, 16 May 2018 19:40:22 GMT

Hi Jean,

Removed them but still no success

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

Anonymous — Wed, 16 May 2018 19:45:16 GMT

Load

Num(Num#(Data)) as Data1,

Data2;

SQL SELECT

Data,

Data2

FROM "MasterData";

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

vvira1316 — Wed, 16 May 2018 19:48:42 GMT

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.

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

mrichman — Wed, 16 May 2018 19:57:08 GMT

It works! Thanks for your help Robin.

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

mrichman — Wed, 16 May 2018 19:57:47 GMT

Vijay,

Thanks for you help, it is already solved by:

Load

Num(Num#(Data)) as Data1,

Data2;

SQL SELECT

Data,

Data2

FROM "MasterData";

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

Anonymous — Wed, 16 May 2018 19:58:28 GMT

welcome, ... so your number was not a number

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

mrichman — Wed, 16 May 2018 20:01:07 GMT

Yes you are right, the dataset is quite toxic. It is a mix of numbers and texts but number format is leading. Do you have any other suggestions?