topic Re: How-To: Import a field in a csv whithout decimal in QlikView

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

Fri, 20 Jul 2012 13:47:04 GMT

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?

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

Fri, 20 Jul 2012 14:28:14 GMT

Hey,

You could use the round function in your script

round(M_DEVI_COMPTA_C , 0.01 )

Hope this helps.

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

Fri, 20 Jul 2012 14:32:58 GMT

Thank's frankcrezee

I tried it but it's not better.

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

swuehl — Fri, 20 Jul 2012 14:33:11 GMT

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

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

Fri, 20 Jul 2012 14:37:30 GMT

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

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

swuehl — Fri, 20 Jul 2012 14:50:02 GMT

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?

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

Fri, 20 Jul 2012 14:56:02 GMT

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

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

swuehl — Fri, 20 Jul 2012 15:00:31 GMT

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?

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

Tue, 24 Jul 2012 13:45:08 GMT

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.