topic Re: How to remove decimals during load in QlikView

How to remove decimals during load

iamthefixer — Tue, 07 Oct 2014 15:12:35 GMT

Hello, I am loading hundreds of .xlsm files and found that values that are formatted as General in Excel are imported with 6 decimals into QV.

For example

Excel	QV
61040	61040.000000

What is the solution to get the following result in QV?

Expected result

Excel	QV
61040	61040

FYI - I have troed Text() and Num#(), Num() with no success.

Thank you for or support..

Andrea

Re: How to remove decimals during load

anbu1984 — Tue, 07 Oct 2014 15:13:51 GMT

Can you attach sample file

Re: How to remove decimals during load

anbu1984 — Tue, 07 Oct 2014 15:18:16 GMT

Try this

Load Your_Amt_col+0 from Table

Re: How to remove decimals during load

iamthefixer — Tue, 07 Oct 2014 15:23:00 GMT

Anbu, thank you for reply.

What does Your_Amt_col+0 mean

Andrea

Re: How to remove decimals during load

JonnyPoole — Tue, 07 Oct 2014 15:26:37 GMT

What do you have set at the top of the load script for these 2 variables ? Ensure they are set correctly for your data format

SET ThousandSep=',';

SET DecimalSep='.';

As long as those are set you can use round() to remove any decimals in the load script. num() doesn't remove decimals from the data model it just sets the display formatting.

change:

Load

Numberfield

from <source>;

to:

Load

round(Numberfield) as Numberfield

from <source>;

Re: How to remove decimals during load

iamthefixer — Tue, 07 Oct 2014 15:29:31 GMT

Anbu,

I have foudn the solution: num(num#(FIELD))

Many thanks for your support.

Andrea