topic How can I convert a text into a number? in QlikView

How can I convert a text into a number?

rcandeo — Fri, 25 May 2012 17:29:04 GMT

We use SAP and the material number (MATNR) is a field with lenght 18 and char.

We only use numbers but when I bring to qlik view I receive the information like this:

000000000000123456

Even if I use in the script Num(MATNR) or NUM(MATNR, '0'), I still get the result like 000000000000123456

I tried to change in the properties of the document in the NUMBER tag that the MATNR is a number but with no sucess.

How can I make this transformation to have only 123456?

Thank you

How can I convert a text into a number?

Fri, 25 May 2012 17:35:30 GMT

Hi,

try to use : text(MATNR) . I hope this can help you.

Thanks.

Regards,

Heri

How can I convert a text into a number?

Jason_Michaelides — Fri, 25 May 2012 17:35:30 GMT

Have you tried using Num#(MANTR)?

How can I convert a text into a number?

rcandeo — Fri, 25 May 2012 17:53:53 GMT

No worked TEXT(MATNR), neither NUM#(MATNR)

How can I convert a text into a number?

Jason_Michaelides — Fri, 25 May 2012 17:58:38 GMT

Try some of the solutions here:

http://community.qlik.com/message/63300#63300

How can I convert a text into a number?

Fri, 25 May 2012 18:32:09 GMT

Hi rcandeo

Create a parameter variable in your script such as:

// ------------------------------------------------------------------

// Take off leading zeros and convert to number

// Usage: $(vCHAR2NUM(MATNR)) AS [Material_MATNR]

// ------------------------------------------------------------------

SET vCHAR2NUM = REPLACE(LTRIM(REPLACE($1, '0', ' ')), ' ', 0);

Then In your Load

Tablename:

Load $(vCHAR2NUM(MATNR)) AS [Material_MATNR],

etc.

and that will convert any 'NumChar' to a Number.

Cheers.

Dave.

How can I convert a text into a number?

rcandeo — Fri, 25 May 2012 19:14:03 GMT

All answer you people are giving to me are very useful, but only work with the QVW that get the data directly on SAP.

After bringing the data from SAP and generating a QVD file, if I try to open this QVD file and aply any of these sugestions, they don´t work. Any idea why?

How can I convert a text into a number?

erichshiino — Fri, 25 May 2012 19:29:52 GMT

Hi, QV supports a maximum of 14 digits in your number. If it is longer than that, it will be interpreted a a text.

Maybe when you load it and export it to QVD with the zeros on the left, QV will interpret it incorrectly again when the QVD is loaded.

First, you can use evaluate() to convert your original text into a integer.Then, you can use NUM to format it appropriately

In the example below, the operation in EVLN2 is probaly the one that you need.

LOAD

VERY_LONG_NUMBER,

NUM(VERY_LONG_NUMBER) AS NUMVLN,

EVALUATE(VERY_LONG_NUMBER) AS EVLN ,

num( EVALUATE(VERY_LONG_NUMBER),'#0') AS EVLN2

INLINE [

VERY_LONG_NUMBER

0000000000000000132123

];

Hope this helps,

Erich

Re: How can I convert a text into a number?

Wed, 04 Nov 2015 20:55:51 GMT

This worked perfectly in the initial extraction of data (using SAP Connector) from SAP for Material Number MATNR:

// VBAP (Sales Document:Item Data)

[VBAP]:

Load

[VBELN] ,

[POSNR] ,

[MATNR] ,

NUM(EVALUATE([MATNR]),'#0') as 'MATNR_SHRT',

[PRODH] as [Product Hierarchy_PRODH.VBAP],

[NETWR] as [Net Value_NETWR.VBAP],

[WAERK] as [SD Document Currency_WAERK.VBAP],

[KWMENG] as [Cumulative Order Qty_KWMENG.VBAP],

[VRKME] as [Sales Unit_VRKME.VBAP],

[BRGEW] as [Gross Weight_BRGEW.VBAP],

[NTGEW] as [Net Weight Item_NTGEW.VBAP],

[GEWEI] as [Weight Unit_GEWEI.VBAP],

[WERKS] as [Plant_WERKS.VBAP],

[VSTEL] as [Shipping Point/Receiving Pt_VSTEL.VBAP];

SQL Select VBELN POSNR MATNR PRODH NETWR WAERK KWMENG VRKME BRGEW NTGEW GEWEI WERKS VSTEL from VBAP

where ERDAT>='20130101'

;

STORE VBAP into [lib://SalesExtract/VBAP.qvd] (qvd);

DROP TABLE [VBAP];

However, if you run the above NUM function against data already stored in the QVD, it will return empty (aka: not work)