topic Re: Converting string to numbers in QlikView

Converting string to numbers

Fri, 30 Nov 2012 15:14:24 GMT

Hi:

I am trying to convert a field that can have a possible of 30 number with a max of 15 decimals in QlikView. I am doing,

Num(A_Value, '0.0')

Can someone let me know if this is correct

Thanks,

Tak

Re: Converting string to numbers

Fri, 30 Nov 2012 16:15:40 GMT

if you are trying to use that field in expression you can limit it to 2 decimals by changing Number tab in properties....

If you are using to represent only that value into interger than use floor() function.....

Re: Converting string to numbers

Fri, 30 Nov 2012 16:28:43 GMT

I actually need to do a compare to make sure the full values matches another field so I will need to compare on what ever the actuall value is. I am new to QlikView and would appreciate it if you can show me the actual code change base on the below.

Num(A_Value, '0.0')

Thanks,

Re: Converting string to numbers

Fri, 30 Nov 2012 16:28:53 GMT

Num(A_Value, '0.0')

Thanks,

Re: Converting string to numbers

Fri, 30 Nov 2012 16:40:19 GMT

can you attach your qvw file....I am not clear with that requirement.....

Re: Converting string to numbers

Fri, 30 Nov 2012 17:06:01 GMT

I am not sure attaching the qvw will help because we were ask to use a framework that was given to us but I had pasted the scripts below. The key is where I am trying to convert the A_value and B_value. The script is in the transform folder and these values will than be used to compare to each other. Hope this helps to clarify.

//One tab per dimension

LET vTableName = 'Final_Exchange_Rate'; //!!!What you want to call the table in the QV schema
LET vReloadTimeIn = Now();

[$(vTableName)]:
//!!!Insert Select/Load statement here

LOAD 50 as RECON_ID,

     #keyID,
     A_ID,
    Num(A_Value,'0.0') as A_Value,   /////convert to number with decimal
     B_ID,
     Num(B_Value,'0.0') as B_Value   ////convert to number with decimal
FROM
[..\QVD_Transform\Compare_Exchange_Rate.qvd]
(qvd);

LET vReloadTimeOut = now();

Meta:
LOAD '$(vTableName)' as Table,
NoOfRows('$(vTableName)') as Rows,
NoOfFields('$(vTableName)') as Fields,
'$(vReloadTime)' as ReloadTime,
Interval('$(vReloadTimeOut)' - '$(vReloadTimeIn)','h:m:ss') as ReloadInterval,
1 as LatestFlag
AUTOGENERATE 1;

STORE [$(vTableName)] INTO [..\QVD_Transform\$(vTableName).qvd] (qvd); ////don't touch this
DROP TABLE [$(vTableName)];

Re: Converting string to numbers

Fri, 30 Nov 2012 17:06:15 GMT

//One tab per dimension

LET vTableName = 'Final_Exchange_Rate'; //!!!What you want to call the table in the QV schema
LET vReloadTimeIn = Now();

[$(vTableName)]:
//!!!Insert Select/Load statement here

LOAD 50 as RECON_ID,

LET vReloadTimeOut = now();

STORE [$(vTableName)] INTO [..\QVD_Transform\$(vTableName).qvd] (qvd); ////don't touch this
DROP TABLE [$(vTableName)];

Re: Converting string to numbers

Fri, 30 Nov 2012 17:31:43 GMT

Num(A_Value, '0.0')

I am using above function it is working fine...

I have A_values as

0.00002

0.00033

0.02344

0.004455

0.12

when I use

Num(A_Value, '0.0')

I am getting

0.0

0.1

when I use

Num(A_Value, '0.000')

I am getting

0.000

0.023

0.004

0.120

This is working fine

Re: Converting string to numbers

Fri, 30 Nov 2012 17:58:44 GMT

How do I get it to ensure that it is comparing what ever it is in the A_value and B_value. If A_value is 1234.333444, I need to ensure that it is comparing what ever maybe in that fields. If I were to use the code that I did, I will only get 4.3.

B_value may have 4.344 and the result would be 4.3 and will consider it to be a match when it should not be.

Num(A_Value, '0.0')

Thanks,

Re: Converting string to numbers

shree909 — Fri, 30 Nov 2012 18:06:04 GMT

Hi try this

NUM(A_Value ,'#,##0.00') as A_Value..

if u want 3 decimals replace the last 2 zeros with 3...

or NUM(A_Value ,'#,##0.##')

Re: Converting string to numbers

Fri, 30 Nov 2012 18:07:47 GMT

Sry I didnot understand clearly....

If your A_Value is 1234.333444 then you should get 1234.3 not 4.3

check one more time and see my attachment....

Re: Converting string to numbers

Fri, 30 Nov 2012 18:26:18 GMT

B_value may have 4.344 and the result would be 4.3 and will consider it to be a match when it should not be.

Num(A_Value, '0.0')

Thanks,

Re: Converting string to numbers

MayilVahanan — Fri, 30 Nov 2012 18:35:57 GMT

Have doubt?

do u want like this?

1234.333444 -> 4.3

4.344 -> 4.3

are you want first decimal value. and ones in the number?

Re: Converting string to numbers

CELAMBARASAN — Fri, 30 Nov 2012 18:43:21 GMT

Hi,

You can try by using Num#(A_Value,'#')

Re: Converting string to numbers

Fri, 30 Nov 2012 19:15:17 GMT

Hi:

I really need the value to be the same without any truncation or rounding.

if it have 20 digits and 10 decimal places, I need to keep it as is. Or if it has 15 digings and 5 decimal places, I need to also keep it as it.

Thanks,

Re: Converting string to numbers

Fri, 30 Nov 2012 19:15:21 GMT

Hi:

I really need the value to be the same without any truncation or rounding.

if it have 20 digits and 10 decimal places, I need to keep it as is. Or if it has 15 digings and 5 decimal places, I need to also keep it as it.

Thanks,

Re: Converting string to numbers

Fri, 30 Nov 2012 19:19:07 GMT

Will this keep the value the same regardless of how many digits or decimals?

Thanks,

Re: Converting string to numbers

Fri, 30 Nov 2012 19:19:12 GMT

Will this keep the value the same regardless of how many digits or decimals?

Thanks,

Re: Converting string to numbers

CELAMBARASAN — Fri, 30 Nov 2012 21:06:25 GMT

Yes it will keep the whole thing. Didn't you checked it?

Re: Converting string to numbers

Fri, 30 Nov 2012 21:10:31 GMT

It didn't seem to work. I was getting all the digits with 1 decimal place. I will check it again.

Thanks,