How can I convert a text into a number?

[rcandeo]

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

[#2]

Hi,

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

Thanks.

Regards,

Heri

[Jason_Michaelides]

Have you tried using Num#(MANTR)?

[rcandeo]

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

[Jason_Michaelides]

Try some of the solutions here:

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

[#6]

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.

[rcandeo]

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?

[erichshiino · Accepted solution]

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

[#9]

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)