topic Re: Number being Converted into Mixed Format. in QlikView

Number being Converted into Mixed Format.

Anonymous — Mon, 23 Nov 2015 13:34:29 GMT

Hi,

I have a field whose values(Numbers) are being represented as Text and number(some as number and some as Text). I want them to be represented as number as I have to compare with the same field in other table. I tried NUM and NUM# in the script, but as there are millions of records, the conversion is taking too long (around 2 hrs). So is there any faster way to do the conversion?

Re: Number being Converted into Mixed Format.

sinanozdemir — Mon, 23 Nov 2015 13:46:49 GMT

Hi Uday,

Are you doing the conversion in the load script or in a straight/pivot table?

Re: Number being Converted into Mixed Format.

tamilarasu — Mon, 23 Nov 2015 13:51:49 GMT

Try the below in load script,

Num(Evaluate(YourField),'##.#')

Re: Number being Converted into Mixed Format.

Peter_Cammaert — Mon, 23 Nov 2015 13:51:50 GMT

Properly formatted numbers should be converted automatically by QV. If you display this field in a listbox, is there any obvious difference between numbers that are represented as numerical values, and numbers that are represented as strings (for example wrong decimal/thousand separators, alphabetic characters characters inside the numbers)?

Best,

Peter

Re: Number being Converted into Mixed Format.

Anonymous — Mon, 23 Nov 2015 14:12:32 GMT

Hi,

I am doing the conversion in Load Script.

Re: Number being Converted into Mixed Format.

Anonymous — Mon, 23 Nov 2015 14:15:17 GMT

Hi Peter,

In list box, the values are being displayed as follows,

Field

123456

123457

123458

123459

123460

475380

Re: Number being Converted into Mixed Format.

sinanozdemir — Mon, 23 Nov 2015 14:17:07 GMT

What is the source of your data, SQL Server, Excel, or something else? You said that the conversion is taking too long, is it taking less time without the conversion?

Thanks

Re: Number being Converted into Mixed Format.

Anonymous — Mon, 23 Nov 2015 14:33:41 GMT

The data is already loaded into qvd's and the source for those qvd's is SAP. The time taken without conversion is around 15 mins.

Re: Number being Converted into Mixed Format.

Mon, 23 Nov 2015 14:43:38 GMT

You can go to presentation tab and check on alignment and make the test and number both to left.

Re: Number being Converted into Mixed Format.

Mark_Little — Mon, 23 Nov 2015 15:07:13 GMT

Hi,

Have you tried to add the num() function into the QVW extracting the data and saving the document to QVD's?

It does seem strange as mentioned by other posters it should automatically format as a number unless any strange chars in the field. Mayby try KeepChar(Number,'0123456789') as Number?

Mark

Re: Number being Converted into Mixed Format.

Anonymous — Mon, 23 Nov 2015 15:42:13 GMT

Even though if I do what you have said, the numbers will still be text formatted right?!! which is an issue when comparing with the values with the same field name in other table, which is my requirement.

Re: Number being Converted into Mixed Format.

Mon, 23 Nov 2015 15:52:57 GMT

Oh, Yes.

Maybe you need consider incremental load and convert to number based on time stamp if those records not change very frequently.

Re: Number being Converted into Mixed Format.

Peter_Cammaert — Mon, 23 Nov 2015 15:59:27 GMT

When reading data from SAP, you may have to apply trim() to get rid of various leading and trailing whitespace. IMHO this isn't always done automatically like when reading from Excel or another file format.

You can do this even when reading QVD's that are created during a dedicated SAP export.

Re: Number being Converted into Mixed Format.

Anonymous — Mon, 23 Nov 2015 17:03:02 GMT

Hi,

I did try to check the value of text(number) using text box and expression,

=num(Field)

which is giving me null value.

Re: Number being Converted into Mixed Format.

Peter_Cammaert — Mon, 23 Nov 2015 17:11:44 GMT

Num() returns NULL if Field doesn't contain a numerical value (text only). You should correct the text string before trying to format it using Num().

Peter

Re: Number being Converted into Mixed Format.

tamilarasu — Mon, 23 Nov 2015 17:15:55 GMT

Evaluate function will convert the text to integer. You can try the below script and check whether the format converted or not .

Load Evaluate(yourfield) as Num,

Num(Evaluate(yourfield) as FNum

From

Your qvd path.

Re: Number being Converted into Mixed Format.

Anonymous — Mon, 23 Nov 2015 17:41:38 GMT

Hi,

It converted to integer, but the value is like this

Text Integer

12345908 41439707

but I require

Text Integer

12345908 12345908

Re: Number being Converted into Mixed Format.

tamilarasu — Tue, 24 Nov 2015 04:47:53 GMT

Could you attach some sample data. For me, it's working fine. See below.

Re: Number being Converted into Mixed Format.

jagan — Tue, 24 Nov 2015 05:05:16 GMT

HI,

Try like this

Alt(Num(FieldName), FieldName)

Regards,

Jagan.

Re: Number being Converted into Mixed Format.

NareshGuntur — Tue, 24 Nov 2015 06:20:37 GMT

Have you tried converting the field to number in Number tab under Document Properties?

Cheers,

Naresh