Hi Scott and welcome to the forum.
I think we could benefit from a little more information about what the model in the project does, whether any filters are applied on import, how the databases are linked, any sorts that you might be applying and whether this is a 'frozen' database or something with current activity,
Also how many records you are extracting. If it is a very large extracted /Bfile you may just be hitting some of the technical constraints in the underlying database that Monarch uses as a workspace.
That said the chances are that you will need to do some comparisons for the records extracted on different runs in order to get a lead into where the anomaly is coming from. As a start checking the number of records in the table might provide some clues.
Thanks for your reply.
Some useful information to know may be that we are an asset-based lender, and part of our loan process includes tracking ALL of our clients' receivables on a daily basis.
The purpose of the model and project is to summarize our loan exposure to companies that are in debt of multiple clients of ours. For instance, if Big Company "A" manufactures widgets, and three of our clients (that we make loans to) supply them with parts and have open receivables with Big Company "A", we want to know the entire amount open with Big Company "A". All similar debtors (Big Company "A" and its subsidiaries and similar companies, etc.) are grouped into names what we call "Alpha Codes" (which is a field in the database we're extracting from). This "Alpha Code" is assigned to all of our clients' accounts that are associated with Big Company "A". So the final product of the project will show all the "Alpha Codes" for each company with its total loan exposure next to it, in balance descending order by Alpha Code. Does this make sense? The first line of the project is the total of all accounts NOT associated with an Alpha Code (as it has the biggest balance), then it lists all of the balances by Alpha Code, and then finally a summary total at the bottom.
There is one filter applied on import, which filters out all clients that start with "1". The number of debtors associated with all clients are probably in the 20,000-25,000 range, but when filtered I've been getting between 15,340 and 15,350 debtors after opening multiple times. The databases extract client number, debtor number, debtor balance, client name, and alpha code. The databases are changed once at the end of each day. Could the number of records that I have be too much for Monarch's parameters?
I'll continue to try different sorts and pay attention to the number of records extracted for each Alpha Code to see if I can piece anything together. If you have any more ideas of what could be going on please let me know.
Your 15k record total is not a problem. That said I assume from what your wrote above (if my understanding matches what you wrote) that each of these records will be made up from possibly many detail transactions. On the other hand you don't appear to be dealing with large numbers of fields. Based on my understanding of the underlying Monarch technology (see the section of the Help on "Specifations" and perhaps "Settings" for further information) you would need to be pulling in several million detail records, pre summarization, to hit the underlying database size constraints.
Cab any of the lowest level records at some point have the possibility of relating to more than one Alpha Code within some of the groupings?
Are you filters and sorts all based on keyed fields in the source data file(s)?
Could you have any negative values that are not given the corerct sign?
From a single frozen daily data set does the process always extreact the same number of records?
I think that I would seek to try to analyse that by breaking the process up into smaller chunks to see if it is consistent at those levels.
If you find you are getting different numbers of records extracted for some reason it may still be tricky to discover why. Running the extract with and without filters to see if the filter is a factor in the inconsistency may then help.
This may become a rather iterative thread taking one idea after another until some sort of pattern identifies itself.
Thanks again for your reply.
None of the records could relate to more than one Alpha Code, as each record is assigned just one Alpha Code.
The filters and sorts are based on fields that are extracted via Monarch from electronic files (PDF, Text, and Excel) and brought into a data storing software called Alpha Five on a daily basis. Monarch then grabs the two .dbf datases from Alpha Five for use in this project.
There are negative values, and I'm confident that they all have negative signs where they're suppsoed to be, but I still can't get a consistent total. Is it possible that Monarch could leave out some negative values somewhere between the original database and the final project output?
It doesn't always produce the same number of records when I open up the project each time, even when the data is frozen. There's usually a variance between 1-10 records each time. I've gotten the same results even when removing all sorts and filters.
Another thing I've noticed is that some records in the table (usually between 900-1000 of them - it changes every time I open it) have a blank client name field (they're not blank in the database), and these records could be creating the discrepancy in the total. However the debtor number still appears for all, so I'm still able to tell who the record belongs to. Of the records that show up with a blank name, I'd say about 90-95% of them have no balance, but the ones that do, most of them can be paired with each other, one positive, one negative (for example $190.00 & -$190.00, $52.47 & -$52.47), even though they may not appear right next to each other in the table. Also, the records with balances that have no name don't appear anywhere else in the report. So if I see 2 records with balances with a blank name for ABC Company, ABC Company will not appear elsewhere in the report in the records that have a name. Could there be something wrong with the database?
My vote is that the Alpha Five database is somehow corrupt. Can you export the database to another format - like Excel or Access? If so, I would check the number of records exported and the totals. I worked in Alpha Five a few years ago, but don't recall much about it. Does it have a database repair utility?
It intrigues me that the data is blank in some cases in the Monarch extract, but not in the database. Sometimes if a record does not link properly with another this can happen.
Hope that helps.