Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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?

8 Replies
Not applicable

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

Hey,

You could use the round function in your script

round(M_DEVI_COMPTA_C , 0.01 )

Hope this helps.

Not applicable

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

Thank's frankcrezee

I tried it but it's not better.

MVP
MVP

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

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

Not applicable

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

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

MVP
MVP

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

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?

Not applicable

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

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

MVP
MVP

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

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?

Not applicable

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

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.

Community Browser