Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rcandeo
Creator III
Creator III

How can I convert a text into a number?

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

1 Solution

Accepted Solutions
erich_shiino
Partner
Partner

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

View solution in original post

8 Replies
Not applicable

Hi,

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

Thanks.

Regards,

Heri

jason_michaelid
Luminary Alumni
Luminary Alumni

Have you tried using Num#(MANTR)?

rcandeo
Creator III
Creator III
Author

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

jason_michaelid
Luminary Alumni
Luminary Alumni

Try some of the solutions here:

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

Not applicable

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
Creator III
Creator III
Author

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?

erich_shiino
Partner
Partner

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

View solution in original post

Not applicable

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)