Variance in totals appended versus excel

sureshc14

Forums

Hi Brian,
Hope you are doing good. I have a question regarding the differces found in totals with appended database and individual totals of the files.
for eg 12 months data in text imported into idea with four decimal places and to validate the appended database i have taken the total of each file from the field statistics and input the same into excel,  but after appending all the 12 files i am facing a difference of more than 1000 with the appended database and the grand total of excel file. What could be the reason for this??
Thanks for your help.
Regards,
Amar

Brian Element Tue, 11/15/2016 - 13:39

Hi Amar,

I am doing good, thanks for asking, I hope the same is true for you.

That is an interesting problem.  I just want to make sure that I understand what is going on.  So you import a monthly text file.  Once the file has been imported into IDEA you take that monthly total, either from the control total or the field stats and you paste it into Excel.  You do this for each of the 12 files.  You then append the 12 files together into one file and compare the total of the 12 files to the total of the individual monthly file totals that you have copied into Excel?  Am i understanding this correctly?

This is a strange one.  Do the total number of records in the appended file equal to the 12 monthly number of records?  Are you sure you didn't place a criteria and forget to remove it during the append? 

How large are these files?  Is the 1,000 a large difference or a small difference that could be caused by rounding? 

Are your totals with four decimals and is Excel using four decimals? 

These are the kind of mysteries I enjoy :-), hopefully we can figure out what is going on.

Brian

sureshc14 Wed, 11/16/2016 - 10:45

Hi Brian,
Thanks for your response.
Yes. Your understanding is correct.
The total number of records of 12 files is matching with the appended file. I have not given any criteria while appending these 12 files and i have taken four decimals in Idea and excel. These 12 idea files is having more than 12 lakh rows once we convert from text . Even i thought the difference is by rounding but not sure how 15000 of differce came up.
I hope you got a better understanding. It would be great if you provide a solition for this.
Thanks,
Amar

Brian Element Wed, 11/16/2016 - 10:50

Hi Amar,

It is hard for me to see what it going one without seeing the files.  Any chance you can share them?  Or maybe a portion of them.  Either than that I am not sure what the problem could be.

Thanks

Brian

sureshc14 Wed, 11/16/2016 - 11:43

Hi Brian,
I cannot share the files due to its size and due to firm policy i cannot share the files to outside system anyway i can give the screenshot of the files. I am not sure it works for you or not?
Thanks,
Amar