topic Re: Number format in QlikView

Number format

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

HI all i have below data,

I have ID and I want concatinate one fixed number with that ID and have some further caluculation.

My issue is after concatenation my new ID is still in string format and I want in Number format.

Sample:

LOAD ID,

Num#(ID&56431256) as NewID

FROM

(ooxml, embedded labels, table is Sheet1);

Please share script as well , once it done

Re: Number format

Anil_Babu_Samineni — Wed, 02 Aug 2017 05:21:40 GMT

May be this?

Num#(ID) & '56431256' as NewID

Re: Number format

zhadrakas — Wed, 02 Aug 2017 05:22:32 GMT

try like

Num#(Num(ID, '0000000') & Num(56431256, '0000000'), '000000000000000')

it could be a problem if you have 14 or more digits

see here

https://gist.github.com/ralfbecher/6550467‌

Re: Number format

techvarun — Wed, 02 Aug 2017 05:36:23 GMT

Num#(ID)*100000000 + 56431256 as NewID

Re: Number format

Anonymous — Wed, 02 Aug 2017 06:03:20 GMT

Hi,

May be try like this :

Sample:

Load *,

Num(NewID) as NewID_Num;

LOAD ID,

ID&'56431256' as NewID

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Number format

antoniotiman — Wed, 02 Aug 2017 06:22:44 GMT

Hi,

You can't because max nr of digits in QV is 14.

However

LOAD Num(ID&'1234') as NewID
FROM File;

So NewID is Numeric

Regards,

Antonio

Re: Number format

balar025 — Wed, 02 Aug 2017 08:11:09 GMT

QlikView works on AutoCasting. But Your assigning value is getting bigger. So, it is getting convert to text.

Re: Number format

Wed, 02 Aug 2017 10:55:44 GMT

Hi Subbareddy,

Here is the script for this query

Common Id : 1234

Sample:

LOAD ID

FROM

[..\..\Downloads\Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Sample1:

LOAD num(num(ID & 1234,'00000000000'),'00000000000')as NewId

Resident Sample;

DROP TABLE Sample

Re: Number format

Wed, 02 Aug 2017 10:56:36 GMT

LOAD

ID,Num(Num(ID,'0000000')&Num(564312,'000000'),'000000000000')

FROM (ooxml, embedded labels, table is Sheet1);

It is working if the total digits are less than 14.