How-To: Import a field in a csv whithout decimal

[#1]

Hello,

I have a trouble when I import a csv in QV, the field got too many decimal.

My field is M_DEVI_COMPTA_C :

2 327 003 521,8200002000 but in my file I have only : 2 327 003 521,82

I want to delete this decimal because when I make a sum on multiple lines I have some difference with excel.

Do you have any idea?

[#2]

Hey,

You could use the round function in your script

round(M_DEVI_COMPTA_C , 0.01 )

Hope this helps.

[#3]

Thank's frankcrezee

I tried it but it's not better.

[swuehl]

I'm not 100% sure, but I believe that this is not an import issue, but an issue with floating point representation.

You just can't represent any number possible exactely using floating points.

So QV does store your number internally with these decimals, I think there is no way around if you keep to floating points. Normally, you won't really care, since the error is neglectible.

You are saying that you are already encountering problems when summing up all your numbers. You might want to consider working with integers only (Multiply your input values with 100 and use floor to convert to integers. Then do all your calculations with the integers and only convert back to a number with 2 decimals when displaying data).

Hope this helps,

Stefan

[#5]

And if I want to put it in a QVD, is it possible to avoid this issue?

[swuehl]

I am not sure how a qvd should help here. If my assumption is right, it's all about the internal number storage, so as soon as you've loaded your data from any source, you are running into the same issue as long as you are using floating point binary representation.

Maybe I've misunderstood your question?

[#7]

No you've understood, I just wanted to avoid editing all my expression in my application and do it in my ETL.

In my expression I put :

sum(M_DEVI_COMPTA_C*100))/100

It's better

Thanks swuehl

[swuehl]

You can create an integer field in the ETL part / load script, like

LOAD

...

floor(M_DEVI_COMPTA_C*100) as M_DEVI_COMPTA_C_INT,

...

May I ask how many records you are summing up? How big is the error in your sum?

[#9]

Thank you for the script but I will have to modify all expressions of my objects.

I have millions of records and the difference in my sum is around 2 cents.